Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

DECLARIING VARIABLE AND INCORRECT SYNTAX

Posted on 2008-10-14
4
Medium Priority
?
327 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
4 Comments
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 900 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

722 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