Solved

DECLARIING VARIABLE AND INCORRECT SYNTAX

Posted on 2008-10-14
4
316 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
error in my cursor 5 50
Procedure syntax 5 48
Limit number of characters returned to 999 9 31
online  environment for testing sql queries 5 29
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.…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

740 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