Link to home
Start Free TrialLog in
Avatar of Mahonek
Mahonek

asked on

Syntax Error: Need Help Rewriting Access Query to SQL Script

Hello programmers.  I am attempting to rewrite the following MS Access Query in to SQL Script.  But I'm not sure how to handle the "AND" part in line 3 of the SQL script below.  can someone help me with that syntax?  Thanks

/*ORGINAL QUERY SCRIPT:*/
SELECT DISTINCTROW IIf(Left([el2],3)='002','299999',IIf(Left([el2],3)='003' And [el2]<>'003035','399999','999999')) AS exp1,
testit2.el1, Sum(testit2.Actual) AS SumOfActual, Sum(testit2.Budget) AS SumOfBudget, Sum(testit2.Prior_Act) AS SumOfPrior_Act,
Sum(testit2.Actual_YTD) AS SumOfActual_YTD, Sum(testit2.Budget_YTD) AS SumOfBudget_YTD, Sum(testit2.Prior_YTD) AS SumOfPrior_YTD,
testit2.year, testit2.period, testit2.base_grp
FROM testit2
GROUP BY IIf(Left([el2],3)='002','299999',IIf(Left([el2],3)='003' And [el2]<>'003035','399999','999999')), testit2.el1, testit2.year,
testit2.period, testit2.base_grp;

/*CONVERTED SQL SCRIPT:*/
SELECT DISTINCT
CASE WHEN(Left([el2],3))='002'THEN'299999'
WHEN(Left([el2],3))='003'And [el2]<>'003035','399999','999999')) AS exp1,
oas_testit2.el1,
Sum(oas_testit2.Actual) AS SumOfActual,
Sum(oas_testit2.Budget) AS SumOfBudget,
Sum(oas_testit2.Prior_Act) AS SumOfPrior_Act,
Sum(oas_testit2.Actual_YTD) AS SumOfActual_YTD,
Sum(oas_testit2.Budget_YTD) AS SumOfBudget_YTD,
Sum(oas_testit2.Prior_YTD) AS SumOfPrior_YTD,
oas_testit2.year, oas_testit2.period, oas_testit2.base_grp
FROM oas_testit2
GROUP BY
CASE WHEN(Left([el2],3)='002'THEN'299999'
WHEN(Left([el2],3)='003' And [el2]<>'003035','399999','999999'))
oas_testit2.el1,
oas_testit2.year,
oas_testit2.period,
oas_testit2.base_grp


ASKER CERTIFIED SOLUTION
Avatar of Daniel Wilson
Daniel Wilson
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial