Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 331
  • Last Modified:

DECLARIING VARIABLE AND INCORRECT SYNTAX

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
Mahonek
Asked:
Mahonek
  • 3
1 Solution
 
BrandonGalderisiCommented:
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
 
MahonekAuthor Commented:
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
 
MahonekAuthor Commented:
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
 
MahonekAuthor Commented:
The solution had about a third of the solution.  Thanks.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now