Solved

DECLARIING VARIABLE AND INCORRECT SYNTAX

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…

911 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

25 Experts available now in Live!

Get 1:1 Help Now