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'.
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");
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.