What I want to do is because I cannot perform cursor transactions inside an INNER JOIN, I need to take the following portion out 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 teh 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 first INNER JOIN in my proc below
So in other words, the first inner join will in the end look like this:
INNER JOIN
(
DECLARE CheckCodes CURSOR FOR
SELECT SUM(rmstranamt)FROM #MYTEMPTABLE
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
Then I need to reverence that temp table instead of teh 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 first INNER JOIN in my proc below
So in other words, the first inner join will in the end look like this:
INNER JOIN
(
DECLARE CheckCodes CURSOR FOR
SELECT SUM(rmstranamt)FROM #MYTEMPTABLE
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