Solved

Syntax Error: Need Help Rewriting Access Query to SQL Script

Posted on 2008-10-03
1
169 Views
Last Modified: 2010-03-20
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


0
Comment
Question by:Mahonek
1 Comment
 
LVL 32

Accepted Solution

by:
Daniel Wilson earned 75 total points
ID: 22637434
You're close.  Try this for the CASE statement:

CASE WHEN Left([el2],3))='002'THEN'299999'
      WHEN Left([el2],3))='003' And [el2]<>'003035' THEN '399999'
     ELSE '999999' END AS exp1,

Open in new window

0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
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.​
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.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

813 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

18 Experts available now in Live!

Get 1:1 Help Now