MS Query Case Statement

[MattConnell]

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]

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]

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]

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