Solved

DECLARIING VARIABLE AND INCORRECT SYNTAX

Posted on 2008-10-14
4
300 Views
Last Modified: 2010-04-21
Hello programmers.  I am getting the following declaration and syntax errors in my SQL script.  
1. Can I declare my variables with values for testing.  I will be passing the values thru my VB script.  
2. Can I use the <int> next to my parameter?
3. I seem to be having problems in my "WHERE" script.
Can someone help me with resolving them?  Thanks.

SQL SCRIPT
@par_YR smallint,
@par_MTH smallint

@par_YR smallint = '2008'
@par_MTH smallint = '6'

SELECT DISTINCT oas_balance_test.cmpcode, oas_balance_test.el1, oas_balance_test.el2, oas_balance_test.full_value,
CASE WHEN balcode='ACTUAL' And yr=@par_YR And period=@par_MTH THEN full_value ELSE 0 END AS ActAmt_Base,
CASE WHEN balcode='BUDGET' And yr=@par_YR And period=@par_MTH THEN full_value ELSE 0 END AS BudAmt_base,
CASE WHEN balcode='ACTUAL' And yr=Int@par_YR-1 And period=@par_MTH THEN full_value ELSE 0 END AS LYAmt_base,
CASE WHEN balcode='ACTUAL' And [period]<=@par_MTH And [year]=@par_YR THEN full_value ELSE 0 END AS ActYtd_base,
CASE WHEN balcode='BUDGET' And [period]<=@par_MTH And [year]=@par_YR THEN full_value ELSE 0 END AS BudYTD_Base,
CASE WHEN balcode='ACTUAL' And [period]<=@par_MTH And [year]=Int@par_YR-1 THEN full_value ELSE 0 END AS LYYTD_Base,
oas_balance_test.balcode, oas_balance_test.yr AS [yr], oas_balance_test.period, oas_balance_test.repbasis,
CASE WHEN(Left([el1],2))='41'THEN'FBASE',
CASE WHEN(Left([el1],2))='42'THEN'WBASE',
CASE WHEN(Left([el1],2))='43'THEN'LBASE',
CASE WHEN(Left([el1],2))='44' Or Left([el1],2))='45'THEN'BBASE',
CASE WHEN(Left([el1],2))='46'THEN'CBASE',
CASE WHEN(Left([el1],2))='47'THEN'RBASE',
CASE WHEN(Left([el1],2))='48'THEN'FBASE',
CASE WHEN(Left([el1],2))='40'THEN'FBASE'ELSE'PROB'END AS basegrp INTO oas_bases1temp
FROM oas_ilfbasegrp1 INNER JOIN oas_balance_test ON oas_ilfbasegrp1.account = oas_balance_test.el1
WHERE (((oas_balance_test.cmpcode)='FORNAIO') AND ((oas_balance_test.el1) Between '40000' And '49999')
AND ((oas_balance_test.balcode)='ACTUAL' Or (oas_balance_test.balcode)='BUDGET') AND ((oas_balance_test.yr)=@par_YR
Or (oas_balance_test.yr)=(@par_YR-1)) AND ((oas_balance_test.period)<=@par_MTH) AND ((oas_balance_test.repbasis)='3'));
ERRORS:
Server: Msg 137, Level 15, State 2, Line 2
Must declare the variable '@par_YR'.
Server: Msg 137, Level 15, State 1, Line 9
Must declare the variable '@par_YR'.
Server: Msg 170, Level 15, State 1, Line 25
Line 25: Incorrect syntax near 'oas_balance_test'.
0
Comment
Question by:Mahonek
  • 3
4 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 300 total points
ID: 22717026
Those look like SQL server error message so I'll fix you up with SQL Server syntax.

You didn't have a declare for your variables or SET to set the value.
declare @par_YR smallint,

@par_MTH smallint
 

set @par_YR smallint = '2008'

set @par_MTH smallint = '6'
 

SELECT DISTINCT oas_balance_test.cmpcode, oas_balance_test.el1, oas_balance_test.el2, oas_balance_test.full_value, 

CASE WHEN balcode='ACTUAL' And yr=@par_YR And period=@par_MTH THEN full_value ELSE 0 END AS ActAmt_Base, 

CASE WHEN balcode='BUDGET' And yr=@par_YR And period=@par_MTH THEN full_value ELSE 0 END AS BudAmt_base, 

CASE WHEN balcode='ACTUAL' And yr=Int@par_YR-1 And period=@par_MTH THEN full_value ELSE 0 END AS LYAmt_base, 

CASE WHEN balcode='ACTUAL' And [period]<=@par_MTH And [year]=@par_YR THEN full_value ELSE 0 END AS ActYtd_base, 

CASE WHEN balcode='BUDGET' And [period]<=@par_MTH And [year]=@par_YR THEN full_value ELSE 0 END AS BudYTD_Base, 

CASE WHEN balcode='ACTUAL' And [period]<=@par_MTH And [year]=Int@par_YR-1 THEN full_value ELSE 0 END AS LYYTD_Base, 

oas_balance_test.balcode, oas_balance_test.yr AS [yr], oas_balance_test.period, oas_balance_test.repbasis, 

CASE WHEN(Left([el1],2))='41'THEN'FBASE',

CASE WHEN(Left([el1],2))='42'THEN'WBASE',

CASE WHEN(Left([el1],2))='43'THEN'LBASE',

CASE WHEN(Left([el1],2))='44' Or Left([el1],2))='45'THEN'BBASE',

CASE WHEN(Left([el1],2))='46'THEN'CBASE',

CASE WHEN(Left([el1],2))='47'THEN'RBASE',

CASE WHEN(Left([el1],2))='48'THEN'FBASE',

CASE WHEN(Left([el1],2))='40'THEN'FBASE'ELSE'PROB'END AS basegrp INTO oas_bases1temp

FROM oas_ilfbasegrp1 INNER JOIN oas_balance_test ON oas_ilfbasegrp1.account = oas_balance_test.el1

WHERE (((oas_balance_test.cmpcode)='FORNAIO') AND ((oas_balance_test.el1) Between '40000' And '49999') 

AND ((oas_balance_test.balcode)='ACTUAL' Or (oas_balance_test.balcode)='BUDGET') AND ((oas_balance_test.yr)=@par_YR 

Or (oas_balance_test.yr)=(@par_YR-1)) AND ((oas_balance_test.period)<=@par_MTH) AND ((oas_balance_test.repbasis)='3'));

ERRORS:

Server: Msg 137, Level 15, State 2, Line 2

Must declare the variable '@par_YR'.

Server: Msg 137, Level 15, State 1, Line 9

Must declare the variable '@par_YR'.

Server: Msg 170, Level 15, State 1, Line 25

Line 25: Incorrect syntax near 'oas_balance_test'.

Open in new window

0
 

Author Comment

by:Mahonek
ID: 22717103
OK, AFTER RUNNING YOUR SCRIPT, I GOT THE FOLLOWING ERRORS BUT I THINK I KNOW WHAT'S GOING ON.  LET ME KNOW

YOUR SCRIPT:
declare @par_YR smallint,
@par_MTH smallint
 
set @par_YR = '2008'
set @par_MTH = '6'
 
SELECT DISTINCT oas_balance_test.cmpcode, oas_balance_test.el1, oas_balance_test.el2, oas_balance_test.full_value,
CASE WHEN balcode='ACTUAL' And yr=@par_YR And period=@par_MTH THEN full_value ELSE 0 END AS ActAmt_Base,
CASE WHEN balcode='BUDGET' And yr=@par_YR And period=@par_MTH THEN full_value ELSE 0 END AS BudAmt_base,
CASE WHEN balcode='ACTUAL' And yr=Int@par_YR-1 And period=@par_MTH THEN full_value ELSE 0 END AS LYAmt_base,
CASE WHEN balcode='ACTUAL' And period<=@par_MTH And yr=@par_YR THEN full_value ELSE 0 END AS ActYtd_base,
CASE WHEN balcode='BUDGET' And period<=@par_MTH And yr=@par_YR THEN full_value ELSE 0 END AS BudYTD_Base,
CASE WHEN balcode='ACTUAL' And period<=@par_MTH And yr=Int@par_YR-1 THEN full_value ELSE 0 END AS LYYTD_Base,
oas_balance_test.balcode, oas_balance_test.yr AS yr, oas_balance_test.period, oas_balance_test.repbasis,
CASE WHEN(Left([el1],2))='41'THEN'FBASE',
CASE WHEN(Left([el1],2))='42'THEN'WBASE',
CASE WHEN(Left([el1],2))='43'THEN'LBASE',
CASE WHEN(Left([el1],2))='44' Or Left([el1],2))='45'THEN'BBASE',
CASE WHEN(Left([el1],2))='46'THEN'CBASE',
CASE WHEN(Left([el1],2))='47'THEN'RBASE',
CASE WHEN(Left([el1],2))='48'THEN'FBASE',
CASE WHEN(Left([el1],2))='40'THEN'FBASE'ELSE'PROB'END AS basegrp INTO oas_bases1temp
FROM oas_ilfbasegrp1 INNER JOIN oas_balance_test ON oas_ilfbasegrp1.account = oas_balance_test.el1
WHERE (((oas_balance_test.cmpcode)='FORNAIO') AND ((oas_balance_test.el1) Between '40000' And '49999')
AND ((oas_balance_test.balcode)='ACTUAL' Or (oas_balance_test.balcode)='BUDGET') AND ((oas_balance_test.yr)=@par_YR
Or (oas_balance_test.yr)=(@par_YR-1)) AND ((oas_balance_test.period)<=@par_MTH) AND ((oas_balance_test.repbasis)='3'));


ERRORS;
Server: Msg 170, Level 15, State 1, Line 15
Line 15: Incorrect syntax near ','.
Server: Msg 170, Level 15, State 1, Line 24
Line 24: Incorrect syntax near 'oas_balance_test'.
0
 

Author Comment

by:Mahonek
ID: 22717167
OK, Thanks for helping me onto the right track with the declaration, etc.  I was able to resolve the remaining problems.  for one, I needed to dump the second level of CASE WHENS FOR WHENS here;
CASE WHEN(Left([el1],2))='41'THEN'FBASE',
CASE WHEN(Left([el1],2))='42'THEN'WBASE',
CASE WHEN(Left([el1],2))='43'THEN'LBASE',
CASE WHEN(Left([el1],2))='44' Or Left([el1],2))='45'THEN'BBASE',
CASE WHEN(Left([el1],2))='46'THEN'CBASE',
CASE WHEN(Left([el1],2))='47'THEN'RBASE',
CASE WHEN(Left([el1],2))='48'THEN'FBASE',
CASE WHEN(Left([el1],2))='40'THEN'FBASE'ELSE'PROB'END AS basegrp INTO oas_bases1temp

Second I needed to remove a couple of parenthesis here;
CASE WHEN(Left([el1],2))='44' Or Left([el1],2))='45'THEN'BBASE',

Third I needed to dump the int from my parameters here;
CASE WHEN balcode='ACTUAL' And yr=Int@par_YR-1 And period=@par_MTH THEN full_value ELSE 0 END AS LYAmt_base,

and here;
CASE WHEN balcode='ACTUAL' And period<=@par_MTH And yr=Int@par_YR-1 THEN full_value ELSE 0 END AS LYYTD_Base,
0
 

Author Closing Comment

by:Mahonek
ID: 31506144
The solution had about a third of the solution.  Thanks.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Date Range Syntax Access 2003 10 45
mySql Syntax 7 33
SQL Select Query help 3 32
encyps queries mssql 15 27
Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now