Link to home
Start Free TrialLog in
Avatar of Mahonek
Mahonek

asked on

SQL Syntax Error

Hello programmers.  I am getting the following error to the following SQL script.  Can someone help me identify what it is in my syntax (specifically the variables) that is incorrect?  Can I even use variables when creating a view?

ERROR:
Server: Msg 170, Level 15, State 1, Procedure TopLevel, Line 15
Line 15: Incorrect syntax near '@par_YR'.
Server: Msg 137, Level 15, State 1, Procedure TopLevel, Line 35
Must declare the variable '@par_YR'.
CREATE VIEW TopLevel
 
	@par_YR smallint,
	@par_MTH smallint
 
AS
 
SELECT DISTINCT oas_reppandl_test.cmpcode, oas_reppandl_test.unit, oas_reppandl_test.account, oas_reppandl_test.year, 
oas_reppandl_test.period, oas_reppandl_test.pnl_type, Sum(oas_reppandl_test.Act_Amt) AS SumOfAct_Amt, 
Sum(oas_reppandl_test.Act_Base) AS SumOfAct_Base, Sum(oas_reppandl_test.Act_Basens) AS SumOfAct_Basens, 
Sum(oas_reppandl_test.Bud_Amt) AS SumOfBud_Amt, Sum(oas_reppandl_test.Bud_Base) AS SumOfBud_Base, 
Sum(oas_reppandl_test.Bud_Basens) AS SumOfBud_Basens, Sum(oas_reppandl_test.LY_Amt) AS SumOfLY_Amt, 
Sum(oas_reppandl_test.LY_Base) AS SumOfLY_Base, Sum(oas_reppandl_test.LY_Basens) AS SumOfLY_Basens, 
Sum(oas_reppandl_test.ActYTD_Amt) AS SumOfActYTD_Amt, Sum(oas_reppandl_test.ActYTD_Base) AS SumOfActYTD_Base, 
Sum(oas_reppandl_test.ActYTD_Basens) AS SumOfActYTD_Basens, Sum(oas_reppandl_test.BudYTD_Amt) AS SumOfBudYTD_Amt, 
Sum(oas_reppandl_test.BudYTD_Base) AS SumOfBudYTD_Base, Sum(oas_reppandl_test.BudYTD_Basens) AS SumOfBudYTD_Basens, 
Sum(oas_reppandl_test.LYYTD_Amt) AS SumOfLYYTD_Amt, Sum(oas_reppandl_test.LYYTD_Base) AS SumOfLYYTD_Base, 
Sum(oas_reppandl_test.LYYTD_Basens) AS SumOfLYYTD_Basens
FROM oas_reppandl_test
GROUP BY oas_reppandl_test.cmpcode, oas_reppandl_test.unit, oas_reppandl_test.account, oas_reppandl_test.year, 
oas_reppandl_test.period, oas_reppandl_test.pnl_type
HAVING ((oas_reppandl_test.cmpcode='fornaio') AND (oas_reppandl_test.unit='002999' Or oas_reppandl_test.unit='003999') 
AND (oas_reppandl_test.account Between '40000' And '49999') AND (oas_reppandl_test.year=@par_YR) 
AND (oas_reppandl_test.period=@par_MTH)AND (oas_reppandl_test.pnl_type)="M");

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of jamesgu
jamesgu

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial