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','2 99999',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','2 99999',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'T HEN'299999 '
WHEN(Left([el2],3))='003'A nd [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'TH EN'299999'
WHEN(Left([el2],3)='003' And [el2]<>'003035','399999',' 999999'))
oas_testit2.el1,
oas_testit2.year,
oas_testit2.period,
oas_testit2.base_grp
/*ORGINAL QUERY SCRIPT:*/
SELECT DISTINCTROW IIf(Left([el2],3)='002','2
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','2
testit2.period, testit2.base_grp;
/*CONVERTED SQL SCRIPT:*/
SELECT DISTINCT
CASE WHEN(Left([el2],3))='002'T
WHEN(Left([el2],3))='003'A
oas_testit2.el1,
Sum(oas_testit2.Actual) AS SumOfActual,
Sum(oas_testit2.Budget) AS SumOfBudget,
Sum(oas_testit2.Prior_Act)
Sum(oas_testit2.Actual_YTD
Sum(oas_testit2.Budget_YTD
Sum(oas_testit2.Prior_YTD)
oas_testit2.year, oas_testit2.period, oas_testit2.base_grp
FROM oas_testit2
GROUP BY
CASE WHEN(Left([el2],3)='002'TH
WHEN(Left([el2],3)='003' And [el2]<>'003035','399999','
oas_testit2.el1,
oas_testit2.year,
oas_testit2.period,
oas_testit2.base_grp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.