The forum will be down for scheduled maintenance on Saturday, March 4 beginning at 10am EST. Actual downtime is unknown but may be several hours.

despite the tested the select for the cursor does return rows but something inside the fetch loop went wrong :

    begin
    declare @minValuation_dt date
    declare @acct_grp_id int
    declare @acct_id int
    declare @subAcct_id int
    Declare @Valuation_dt DATE
//  Declare @Acct_grp_ID INTEGER
//  Declare @subAcct_ID INTEGER
    Declare @Value_amt T__money_2
    Declare @Equity_amt T__money_2
    Declare @Cash T__money_2
    Declare @Sub_Acct_Alias_dd VARCHAR(24)
    Declare @Qty_Chk DECIMAL(12,4)
    Declare @Issue_Cnt_Chk INTEGER
    Declare @Book_val_chk T__money_2
    Declare @PnL_ttl T__money_2
    Declare @Load_status CHAR(4)

    declare @prev_dt  date
    declare @Equity_Prev  T__money_2
    declare @Cash_prev  T__money_2
    declare @Qty_prev  DECIMAL(12,4)
    declare @Issue_prev  integer
    declare @book_prev  T__money_2
    declare @i int

    declare csr_latestValuatn CURSOR for 
    SELECT * 
    FROM dba.PF_DailyValuation as a
    where (@acct_grp_id is null or a.acct_grp_id = @acct_grp_id)
     and (@acct_id is null or a.subacct_id in (select subAcct_ID from dba.subAcct where acct_id = @acct_id))
     and (@subAcct_id is null or a.subAcct_id = @subAcct_id)
     and valuation_dt>= 
     (select max(b.valuation_dt) FROM dba.PF_DailyValuation as b
      where b.acct_grp_id=a.acct_grp_id 
        and b.subAcct_ID=a.subAcct_ID
        and (@minValuation_dt is null or b.valuation_dt >=@minValuation_dt)
     )
     order by acct_grp_id, subAcct_ID
    FOR  READ ONLY

    set @i=0

    SELECT a.*, p.Valuation_dt as prev_dt, p.Equity_amt as Equity_Prev, p.Cash as Cash_prev, p.Qty_Chk as Qty_prev,
         p.Issue_Cnt_Chk as Issue_prev, p.Book_val_chk as book_prev
    into #LatestValuatn
    from dba.PF_DailyValuation as a, dba.PF_DailyValuation as p
        where 1=2

    open csr_latestValuatn
    fetch next  csr_latestValuatn into @Valuation_dt, @Acct_grp_ID, 
      @subAcct_ID, @Value_amt, @Equity_amt, @Cash, @Sub_Acct_Alias_dd, 
      @Qty_Chk, @Issue_Cnt_Chk,@Book_val_chk, @PnL_ttl, @Load_status
    IF @@FETCH_STATUS <> 0 
        PRINT '         <<none>>'     
    WHILE @@FETCH_STATUS = 0
    BEGIN
        set @i=@i+1
        /*
        find the latest record prior to @valuation_dt where there
        there is difference in cash, issue count, qty, baook value:
        */
        select p.Valuation_dt as prev_dt, p.Equity_amt as Equity_Prev, p.Cash as Cash_prev, p.Qty_Chk as Qty_prev,
         p.Issue_Cnt_Chk as Issue_prev, p.Book_val_chk as book_prev
         into @prev_dt,  @Equity_Prev,  @Cash_prev,  @Qty_prev,  @Issue_prev,  @book_prev
        from dba.PF_DailyValuation as p
          where p.Acct_grp_ID=@Acct_grp_ID and p.subAcct_ID=@subAcct_ID
            and p.valuation_dt = (select max(b.Valuation_dt) from dba.PF_DailyValuation as b
              where b.Valuation_dt<@Valuation_dt
                and b.Acct_grp_ID=@Acct_grp_ID and b.subAcct_ID=@subAcct_ID
                and (b.Equity_amt<>@Equity_amt or b.Cash<>@Cash or b.Qty_Chk<>@Qty_Chk
                 or b.Issue_Cnt_Chk<>@Issue_Cnt_Chk or b.Book_val_chk<>@Book_val_chk)
             )

        select @Valuation_dt as Valuation_dt, @Acct_grp_ID as Acct_grp_ID,
         @subAcct_ID as subAcct_ID, @Value_amt as Value_amt, @Equity_amt as Equity_amt, @Cash as Cash, 
         @Sub_Acct_Alias_dd as Sub_Acct_Alias_dd, @Issue_Cnt_Chk as Issue_Cnt_Chk,
         @Book_val_chk as Book_val_chk, @PnL_ttl as PnL_ttl, @Load_status as Load_status,
         @prev_dt as prev_dt, @Equity_Prev as Equity_Prev,
         @Cash_prev as Cash_prev, @Qty_prev as Qty_prev,
         @Issue_prev as Issue_prev, @book_prev as book_prev
         into #LatestValuatn

        fetch next csr_latestValuatn into @Valuation_dt, @Acct_grp_ID, 
          @subAcct_ID, @Value_amt, @Equity_amt, @Cash, @Sub_Acct_Alias_dd, 
          @Qty_Chk, @Issue_Cnt_Chk,@Book_val_chk, @PnL_ttl, @Load_status
    END
    close csr_latestValuatn
    deallocate csr_latestValuatn
    select @i,* from #LatestValuatn
end

asked 07 Aug '14, 14:19

gg99's gravatar image

gg99
193192128
accept rate: 0%

edited 07 Aug '14, 18:03

> something inside the fetch loop went wrong

Exactly what went wrong?

(07 Aug '14, 14:57) Breck Carter
Comment Text Removed
Comment Text Removed

on further debugging, I discover the temporary table the does not hold the rows outside the loop I thought temporary table is good at least for the block. I fixed the first select into #LatestValuatn to match the select into #LatestValuatn inside the loop. but still #LatestValuatn does not retain rows after the loop

Actually each tiem teh select into #LatestValuatn inside the while loop will also removes all previous row! so it seems.

how do I explicitly create the temporary table that would hold all the rows inserted in the while loop and allow me to select outside the while loop

(07 Aug '14, 18:41) gg99
Comment Text Removed

I was tempted to delete this thread but here is what I found to be workable

  • explicit create the temp table outside the loop,
  • use insert into with explicit column names and values:

    insert into #LatestValuatn (Valuation_dt, Acct_grp_ID, subAcct_ID, Value_amt, Equity_amt, Cash, Sub_Acct_Alias_dd, qty_Chk, Issue_Cnt_Chk, Book_val_chk, PnL_ttl, Load_status, prev_dt, Cash_prev, Qty_prev, Issue_prev, book_prev) values(@Valuation_dt, @Acct_grp_ID, @subAcct_ID, @Value_amt, @Equity_amt, @Cash, @Sub_Acct_Alias_dd, @qty_Chk, @Issue_Cnt_Chk, @Book_val_chk, @PnL_ttl, @Load_status, @prev_dt, @Cash_prev, @Qty_prev, @Issue_prev, @book_prev) then the rows get retained on completion of while loop

permanent link

answered 07 Aug '14, 22:34

gg99's gravatar image

gg99
193192128
accept rate: 0%

edited 07 Aug '14, 22:43

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×19
×16

question asked: 07 Aug '14, 14:19

question was seen: 627 times

last updated: 07 Aug '14, 22:43