问题描述
所以这是一个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
这篇关于基于嵌套游标的更新的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!