Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Errors with DECLARE statement

Status
Not open for further replies.

dba112233

Programmer
Jan 10, 2006
39
US
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
 
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top