Errors with DECLARE statement

[dba112233]

I am trying to do a cursor and such in one of my INNER JOINS which is part of a bigger SQL statement. I am getting the following errors:

Msg 156, Level 15, State 1, Procedure Balance_Query, Line 61
Incorrect syntax near the keyword 'DECLARE'.
Msg 137, Level 15, State 1, Procedure Balance_Query, Line 71
Must declare the variable '@RMSTRANCDE'.
Msg 137, Level 15, State 1, Procedure Balance_Query, Line 79
Must declare the variable '@RMSTRANCDE'.
Msg 137, Level 15, State 2, Procedure Balance_Query, Line 81
Must declare the variable '@Sum_of_rmstranamt'.
Msg 156, Level 15, State 1, Procedure Balance_Query, Line 106
Incorrect syntax near the keyword 'AS'.


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

 

[SQLDenis]

you can't join on cursors, what are you trying to do?

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[dba112233]

I have to set the value in rmstranamt based on rmstrancde before I sum up rmstranamt

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


alter PROCEDURE [dbo].[Balance_Query]

AS

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

 

[dba112233]

Ok, then how can I take that query in my first inner join, just the first select and the CASE statement and put the results into a temp table at the very beginning of my stored proc. Then the rest of my main query can use the temp table results instead of RFINANL