本文介绍了基于嵌套游标的更新的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

所以这是一个SSRS报告,所以我使用临时表。我有两个表,一个用于交易,我只是使用它拉帐户和金额。第二是被摊销的犯罪信息我试图基于事务调整,但我遇到语法问题。是否允许case语句与游标或更新一起使用?

So this is for a SSRS report so I'm using temp tables. I have two tables, one for transactions that I'm just using it to pull the account and the amounts. The second is amortized delinquency information I'm trying to adjust based on the transaction but I'm running into syntax issues. Are case statements not allowed to be used with cursors or updates?

--Example Transaction:Account 123456 Principal 500.00 Interest 250.00

delinquent 5 months of 200 principal each month, the transaction had  principal amount of 500, the first two are left alone, the third is reduced to 100 to match the total to the transaction amount, leaving a remaining amount of 0

--Delinquent data
-- 11/2015 Prin 200 Int 80 -> Prin 0   Int 0 (running total  Prin 500 Int 250)
-- 10/2015 Prin 200 Int 80 -> Prin 0   Int 10 (running total  Prin 500 Int 250)
--  9/2015 Prin 200 Int 80 -> Prin 100 Int 80 (running total  Prin 500 Int 240)
--  8/2015 Prin 200 Int 80 -> Prin 200 Int 80 (running total  Prin 400 Int 160)
--  7/2015 Prin 200 Int 80 -> Prin 200 Int 80 (running total  Prin 200 Int 80) <- process starts from the oldest and goes up.

这需要使用游标,因为我无法在集合中处理,因为它必须以最老的行,调整,查找剩余的总数,然后进行调整下一行。如果达到交易金额,剩余的行将被清零。然后我将剩余的金额返回到交易表。

This requires the use of a cursor because I cannot process in a set as it has to start with the oldest row, adjust, find the remaining total, then carry on adjusting the next row. If the transaction amount is reached, the remaining rows are zeroed out. I'm then returning the remaining amounts to the transaction table.

Declare  TranCursor Cursor FORWARD_ONLY
For Select LoanNumber, PrincipalCollected, InterestCollected, ServiceFee, PayoffPrincipal,PayoffInterest,PayoffServiceFee
From #transFinal
FOR UPDATE OF PayoffPrincipal,PayoffInterest,PayoffServiceFee


Open TranCursor;
Fetch next from TranCursor into @TranLoan, @TranPrin, @TranInt, @TranServ, @POPrin,@POInt,@POServ
while (@@Fetch_status = 0)
Begin
    --Process this individual loan's transaction by going through each set of amortized amounts starting with the oldest and reducing excess of transaction amounts to zero.
    --eg. delinquent 3 months of 200 principal each month, the transaction had principal amount of 500, the first two are left alone, the third is reduced to 100 to match the total to the transaction amount
    --Transaction Principal 500 Interest 250
    --Delinquent data
    -- 11/2015 Prin 200 Int 80 -> Prin 0   Int 0 (running total  Prin 500 Int 250)
    -- 10/2015 Prin 200 Int 80 -> Prin 0   Int 10 (running total  Prin 500 Int 250)
    --  9/2015 Prin 200 Int 80 -> Prin 100 Int 80 (running total  Prin 500 Int 240)
    --  8/2015 Prin 200 Int 80 -> Prin 200 Int 80 (running total  Prin 400 Int 160)
    --  7/2015 Prin 200 Int 80 -> Prin 200 Int 80 (running total  Prin 200 Int 80) <- process starts from the oldest and goes up.
    Declare  DelqCursor  CURSOR FORWARD_ONLY
    FOR select LoanNumber,DelqPrin ,DelqInt  ,DelqServFee from #dq
        where LoanNumber = @TranLoan
        Order by PaidToDate Asc
        For update OF DelqPrin, DelqInt, DelqServFee;
        Open DelqCursor;
-----------------------------------------------------------------------------------------------------------
-- Processing individual row of delinquent data
-----------------------------------------------------------------------------------------------------------
Fetch next from DelqCursor into  @DelqPrin, @DelqInt, @DelqServ, @DelqPTD --, @POPrin,@POInt,@POServ
while (@@Fetch_status = 0)
    BEGIN


            CASE when @TranPrin = 0 then set @DelqPrin = 0 -- Syntax error on case
                WHEN @TranPrin >0 and @TranPrin > @DelqPrin then -- Syntax error on when
                    set @TranPrin = @TranPrin - @DelqPrin
                    set @ColPrin = @ColPrin + @DelqPrin
                WHEN @TranPrin >0 and @TranPrin < @DelqPrin then
                    set @ColPrin = @ColPrin + @TranPrin
                    set @TranPrin = 0
                    set @DelqPrin = @DelqPrin -@TranPrin
            end

            CASE when @TranInt = 0 then set @DelqInt = 0
                WHEN @TranInt >0 and @TranInt > @DelqInt then
                    set @TranInt = @TranInt - @DelqInt
                    set @ColInt = ColInt + @DelqInt
                WHEN  @TranInt >0 and @TranInt < @DelqInt then
                    set @ColInt = @ColInt + @TranInt
                    set @TranInt = 0
                    set @DelqInt = @DelqInt -@TranInt
            end

            CASE when @TranServ = 0 then set @DelqServFee = 0
                WHEN  @TranServ >0 and @TranServ> @DelqServ then
                    set @TranServ = @TranServ - @DelqServ
                    set @ColServ = ColServ + @DelqServ
                WHEN  @TranServ >0 and @TranServ < @DelqServ then
                    set @ColServ = @ColServ + @TranServ
                    set @TranServ = 0
                    set @DelqServ = @DelqServ -@TranServ
            end

Fetch next from DelqCursor into  @DelqPrin, @DelqInt, @DelqServ, @DelqPTD
End
-----------------------------------------------------------------------------------
--All rows of delinquent data for a single loan have been processed. Now we update the Payoff columns
----------------------------------------------------------------------------------
Set @PoPrin =  @ColPrin
Set @PoInt =  @ColInt
Set @PoServ =  @ColServ
--Todo Finish update statement for outside loop to update transaction table
Close DelqCursor-- Finished with delinquent data for this loan. We close the cursor
Fetch next from TranCursor into @TranLoan, @TranPrin, @TranInt, @TranServ, @POPrin,@POInt,@POServ --Start Processing next loan
End

Close TranCursor
deallocate DelqCursor
deallocate TranCursor

洞察我可以弄清楚为什么我的情况下,当语句给我错误。我看不到任何在msdn语法,将防止case逻辑工作。

I'd appreciate any insight I can get to figure out why my case when statements are giving me errors. I see nothing in the msdn syntax that would prevent case logic from working.

推荐答案

您对 CASE 语句进行编码的方式无效。事件逻辑需要在 SELECT SET 中发生。您 , SET 位于中间, THEN

The way you have coded your CASE statements will not work. Case logic needs to happen within a SELECT, or SET. Yours are just out there, and the SET is in the middle, after the THEN.

您需要将您的逻辑更改为类似于 CASE 的代码。 / p>

You will need to change your logic to something like this for each CASE you have in your code.

IF @TranPrin = 0
    SET @DelqPrin = 0

IF @TranPrin >0 AND @TranPrin > @DelqPrin
BEGIN
    SET @TranPrin = @TranPrin - @DelqPrin
    SET @ColPrin = @ColPrin + @DelqPrin
END

IF @TranPrin > 0 AND @TranPrin < @DelqPrin
BEGIN
    SET @ColPrin = @ColPrin + @TranPrin
    SET @TranPrin = 0
    SET @DelqPrin = @DelqPrin -@TranPrin
END

这篇关于基于嵌套游标的更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-18 21:53
查看更多