CASE problem

[Ward]

Hi ,
My table has this layout :

tab.a, tab.b, tab.c, tab.x, tab.y

I want to do this :

when tab.c=0, select tab.x, else select tab.y

The resultset should thus be : a, b and either x or y
This is what I tried :

SELECT table.a,table.b, CASE table.c WHEN '0' THEN table.x ELSE table.y

It doesn't work.

tia

Ward
ps: iseries, V5R1

[Knut Stolze]

Ward <ki************@for.president.com> wrote:

Hi ,
My table has this layout :

tab.a, tab.b, tab.c, tab.x, tab.y

I want to do this :

when tab.c=0, select tab.x, else select tab.y

The resultset should thus be : a, b and either x or y
This is what I tried :

SELECT table.a,table.b, CASE table.c WHEN '0' THEN table.x ELSE table.y

It doesn't work.

What does "it doesn't work" mean? What's the error message you got?

There is an "END" missing to complete the CASE expression, and you don't
have a FROM clause in your statement.

What are the data types for the columns 'x' and 'y'?

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

[Ward]

Knut Stolze <st****@de.ibm.com> wrote in
news:br**********@fsuj29.rz.uni-jena.de:

Ward <ki************@for.president.com> wrote:

Hi ,
My table has this layout :

tab.a, tab.b, tab.c, tab.x, tab.y

I want to do this :

when tab.c=0, select tab.x, else select tab.y

The resultset should thus be : a, b and either x or y
This is what I tried :

SELECT table.a,table.b, CASE table.c WHEN '0' THEN table.x ELSE
table.y

It doesn't work.

What does "it doesn't work" mean? What's the error message you got?

There is an "END" missing to complete the CASE expression, and you
don't have a FROM clause in your statement.

What are the data types for the columns 'x' and 'y'?

Thx for your reply, I neglected the END statement and there were some
quoting/comma issues as well. The query below gave me my resultset..

Cool thing this CASE statement, you can use it wherever you want in the
SELECT part, and then continue selecting other fields. Thx again.

SELECT BRPSIH.SHMAGC, BRPSIH.SHLREG, BRPSIH.SHREGC, BRPSIH.SHINVD,
BRPSIH.SHSIHN, BRPSID.SDEXOR, BRPSDA.SASKLT, BRPNAM.NMA15A,
BRPNAM.NMNAME, BRPSDA.SAWFSQ, BRPSDA.SAOFCN, BRPPRD.PRPDGR,
BRPPRD.PRCAT1, BRPPRD.PRCAT2, BRPPRD.PRCAT3, BRPSDA.SAPRDC,
BRPSDA.SADESN, BRPSDA.SASVCH, BRPSDA.SASVNP, CASE BRPSDA.SAOFCN WHEN 0
THEN BRPSDA.SAAKPN ELSE BRPSDA.SAAKPL END, BRPSDA.SATPLE, SASVCH*SASVNP

FROM ****.BRIC01.BRPNAM BRPNAM, ****.BRIC01.BRPPRD BRPPRD,
****.BRIC01.BRPSDA BRPSDA, ****.BRIC01.BRPSIH BRPSIH, ****.BRIC01.BRPSID
BRPSID

WHERE BRPSIH.SHSIHN = BRPSDA.SASIHN AND BRPSDA.SASIHN = BRPSID.SDSIHN
AND BRPSDA.SASIDS = BRPSID.SDSIDS AND BRPSDA.SAPRDC = BRPPRD.PRPRDC AND
BRPSIH.SHNAMC = BRPNAM.NMNAMC AND ((BRPSIH.SHINVD>=20030101 AND
BRPSIH.SHINVD<=20030115))