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 |
I was tempted to delete this thread but here is what I found to be workable
|
> something inside the fetch loop went wrong
Exactly what went wrong?
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