Solved

Syntax Error: Need Help Rewriting Access Query to SQL Script

Posted on 2008-10-03
1
168 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
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
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.​
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

728 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

11 Experts available now in Live!

Get 1:1 Help Now