# MS Query Case Statement



## MattConnell (Feb 23, 2011)

All,

Hope you can help here.

Excel 2010 using MS Query to run handcoded SQL to ruturn data from an ORACLE 11G DBMS.

I am trying to use a CASE statement but am confounded with a series of errors that I cannot figure out.  

SELECT 
GORIMMU.GORIMMU_PIDM, 
GORIMMU.GORIMMU_IMMU_CODE,
CASE
WHEN
(GORIMMU.GORIMMU_IMMU_CODE = 'IMMU_PEND')
THEN 'Y'
ELSE 'N'
END AS 'IMMU_PEND_IND',
FROM GENERAL.GORIMMU GORIMMU

This is what I want to run, however every time I do I get some permutation of the "Didnt expect XX after the select column list" where XX is either WHERE or ( or GORIMMU (if i remove the parenthesis from the logic statement)

Any assistance here.....rather new at this so I'm los here. 

Matt.


----------



## xenou (Mar 5, 2011)

I'm not sure that the MSQuery ODBC driver will allow CASE statements.  Probably not, based on your experience.  Maybe just pull in the IMMU Code and then update it to Y or N after it's in Excel.


----------



## Norie (Mar 5, 2011)

Matt

I think xenou is right about the CASE thing, but it might be possible to do what you want in the query - though it might be easier to just do the Excel thing.

What I was thinking was that since your CASE statement is simply checking if the field is 'IMMU_PEND' and returning Y/F you could try an IIf statement.

Or even simply something like this.

SELECT GORIMMU.GORIMMU_PIDM, GORIMMU.GORIMMU_IMMU_CODE,
*(GORIMMU.GORIMMU_IMMU_CODE = 'IMMU_PEND') AS 'IMMU_PEND_IND',
*FROM GENERAL.GORIMMU GORIMMU

That won't return Y/N, not sure what it returns for Oracle but for Access it returns -1 for true and 0 for false.

Actually, I just tested the Iif and it works for Access but again I'm not sure about Oracle.

IIf(GORIMMU.GORIMMU_IMMU_CODE='IMMU_PEND', 'Y', 'N') As 'IMMU_PEND_IND'


----------



## Richard Schollar (Mar 6, 2011)

You have a comma after the SELECT CASE statement and also I suspect you want to surround the column nam,e with square brackets:


```
SELECT 
GORIMMU.GORIMMU_PIDM, 
GORIMMU.GORIMMU_IMMU_CODE,
CASE
WHEN
(GORIMMU.GORIMMU_IMMU_CODE = 'IMMU_PEND')
THEN 'Y'
ELSE 'N'
END AS *[*IMMU_PEND_IND*]* 
FROM GENERAL.GORIMMU GORIMMU
```


----------

