what is wrong with the fetch loop?

[Breck_Carter]

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

[Breck_Carter]

> something inside the fetch loop went wrong

Exactly what went wrong?

[Former Member]

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

[Former Member]

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