Solved

Syntax Error: Need Help Rewriting Access Query to SQL Script

Posted on 2008-10-03
1
172 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

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

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

617 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