January 12, 2006 at 12:43 pm
What I want to do is because I cannot perform cursor transactions inside an INNER JOIN, I need to take the following portion out of my secdond INNER JOIN below and throw that filter into a temp table first at the very top of my entire stored procedure.
Then I need to reverence that temp table instead of the RFINAL table in the rest of my stored procedure.
The part that will put records into my temp table is:
SELECT RMSFILENUM,
RMSTRANCDE,
rmstranamt AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
OPEN CheckCodes
FETCH FROM CheckCodes INTO @RMSTRANCDE, @rmstranamt
CASE WHEN rf.rmstrancde IN ('50','51','52','53')Then
@Sum_of_rmstranamt = @Sum_of_rmstranamt + -ABS(rf.rmstranamt)
WHEN rf.rmstrancde IN ('55','56','57','58') Then
@Sum_of_rmstranamt = @Sum_of_rmstranamt + ABS(rf.rmstranamt)
END
FETCH NEXT FROM CheckCodes INTO @RMSTRANCDE, @rmstranamt
That part will be taken out of my second INNER JOIN in my proc below
So in other words, the second inner join will in the end up looking like this:
INNER JOIN
(
DECLARE CheckCodes CURSOR FOR
END
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
The current stored proc looks like this:
-------------------------------------------
alter PROCEDURE [dbo].[Balance_Query]
AS
<---------------- I WANT THE TEMP TABLE TO BE CREATED HERE
SELECT rm.rmsacctnum,
rf.rmstrancde,
SUM(rf.rmstranamt) AS [Sum rmstranamt],
rf10.rmstranamt10 AS [Sum rmstranamt 10],
-- Balance calculation based on what values are larger vs. smaller and addition or
-- subtraction based on + - of current numbers
CASE WHEN SUM(rf.rmstranamt) > rf10.rmstranamt10 Then
CASE WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
SUM(rf.rmstranamt) - rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
END
-- If both valus are zero, return zero
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 = 0 Then
0.00
WHEN SUM(rf.rmstranamt) = 0 AND rf10.rmstranamt10 <> 0 Then
SUM(rf10.rmstranamt10) + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) <> 0 AND rf10.rmstranamt10 = 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
ELSE
CASE WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
SUM(rf.rmstranamt) + rf10.rmstranamt10
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 < 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 - SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) > 0 AND rf10.rmstranamt10 < 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
WHEN SUM(rf.rmstranamt) < 0 AND rf10.rmstranamt10 > 0 Then
rf10.rmstranamt10 + SUM(rf.rmstranamt)
END
END AS [Balance],
cb.CurrentBalance
FROM RMASTER rm
-- Sum of All transaction amounts wtih code 10
INNER JOIN
(
SELECT RMSFILENUM,
SUM(distinct rmstranamt) AS rmstranamt10
FROM RFINANL
WHERE RMSTRANCDE = '10'
GROUP BY RMSFILENUM
) AS rf10 ON rf10.RMSFILENUM = rm.RMSFILENUM
-- Sum of All transaction amounts that are not code 10
INNER JOIN
(
DECLARE CheckCodes CURSOR FOR
SELECT RMSFILENUM,
RMSTRANCDE,
rmstranamt AS rmstranamt
FROM RFINANL
WHERE RMSTRANCDE <> '10'
OPEN CheckCodes
FETCH FROM CheckCodes INTO @RMSTRANCDE, @rmstranamt
CASE WHEN rf.rmstrancde IN ('50','51','52','53')Then
@Sum_of_rmstranamt = @Sum_of_rmstranamt + -ABS(rf.rmstranamt)
WHEN rf.rmstrancde IN ('55','56','57','58') Then
@Sum_of_rmstranamt = @Sum_of_rmstranamt + ABS(rf.rmstranamt)
END
FETCH NEXT FROM CheckCodes INTO @RMSTRANCDE, @rmstranamt
SELECT @Sum_of_rmstranamt AS rmstranamt
END
) AS rf ON rf.RMSFILENUM = rm.RMSFILENUM
-- Current Balance which shows at the top of the account in RMS
INNER JOIN
(SELECT RMSFILENUM,( (RMSCHGAMT - RMSRCVPCPL)
+(RMSASSCCST - RMSRCVDCST)
+(RMSACRDINT - RMSRCVDINT)
+(UDCCOSTS1 - UDCRECCS1)
+(UDCCOSTS2 - UDCRECCS2)
+(RMSCOST1 - RMSCOST1R)
+(RMSCOST2 - RMSCOST2R)
+(RMSCOST3 - RMSCOST3R)
+(RMSCOST4 - RMSCOST4R)
+(RMSCOST5 - RMSCOST5R)
+(RMSCOST6 - RMSCOST6R)
+(RMSCOST7 - RMSCOST7R)
+(RMSCOST8 - RMSCOST8R)
+(RMSCOST9 - RMSCOST9R)
+(RMSCOST10 - RMSCOST10R)
- RMSXCSRCVS ) as CurrentBalance
FROM RPRDBAL)
AS cb ON cb.RMSFILENUM = rm.RMSFILENUM
-- Only bring back results where the transaction code is one of these
WHERE rf.rmstrancde IN ('10', '16','18','19','30','31','36','37','38','3A','3B','3C','3D','3E','3F','3M','3N','3O','3P','3Q','3R','3T',
'3U','3X','3Z','40','41','42','43','44','45','46','47','48','49','4A','4B','4D','4E','4H','4J','4X','4Z','50','51','52','53',
'55','56','57','58','5A','5B','5C','5P','5Q','5R','5X','5Z')
--Pull only Active Accounts, first change the var status field to numeric for comparison
AND (dbo.GetNumericValue(SUBSTRING(rm.lstsstatus, 1,3)) < 900
OR dbo.GetNumericValue(SUBSTRING(rm.lstsstatus, 1,2)) < 900)
AND rm.rmsacctnum = '4479472700625630'
GROUP BY rm.rmsacctnum, rf.rmstrancde, cb.CurrentBalance, rf10.rmstranamt10, rf.RMSTRANCDE
-- Ensure that Curent Balance is not the same as the actual balance to give us a list of incorrect balances
HAVING cb.CurrentBalance <> SUM(rf10.rmstranamt10) - SUM(rf.rmstranamt)
AND cb.CurrentBalance <> 0.00
January 12, 2006 at 12:56 pm
You can't use a cursor in a derived table, regardless of whether you have atemp table pre-built. And it's a good thing too, because the performance would be hideous.
It all boils down to: "What are you tryin to do?".
It looks like for each RMSFILENUM, you need a SUM() of the various amounts, where each RMSTRANCDE determines whether the amount is a debit or credit.
Is this a correct interprettation ?
SELECT RMSFILENUM,
SUM(
CASE WHEN rf.rmstrancde IN ('50','51','52','53')
THEN (0 - ABS(RMSTRANAMT))
WHEN rf.rmstrancde IN ('55','56','57','58')
THEN ABS(RMSTRANAMT)
END
) As Sum_Of_RMSTRANAMT
FROM RFINANL
WHERE RMSTRANCDE <> '10'
GROUP BY RMSFILENUM
January 12, 2006 at 1:53 pm
THANKS SO MUCH. I didn't know I could do it that way...much more simple.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply