Case in a SELECT [message #7750] |
Fri, 04 July 2003 12:11 |
Katie
Messages: 3 Registered: July 2003
|
Junior Member |
|
|
I need to create colomns in a SELECT base on certains conditions. I know I can use the DECODE function but I have conditions that conbines AND and OR and I don't know how to implement it in a DECODE statement. so I thought I would be better to use the CASE statement. The problem is that I think I cannot do it with SQL PlUS is it possible. ??
Cans somebody help me to implement it with the DECODE ?
Here is an example
SELECT DECODE(greatest(CRPDTA.F4111.ILTDAY,70000), least(CRPDTA.F4111.ILTDAY,153000), 'D',
DECODE(greatest(CRPDTA.F4111.ILTDAY,153000), least(CRPDTA.F4111.ILTDAY,230000), 'E',
DECODE(greatest(CRPDTA.F4111.ILTDAY,230000), least(CRPDTA.F4111.ILTDAY,240000), 'N',
DECODE(greatest(CRPDTA.F4111.ILTDAY,15959), least(CRPDTA.F4111.ILTDAY,70000), 'N','X')))) AS shift,
Abs(CRPDTA.F4111.ILTRQT) AS ILTRQT, CRPDTA.F4111.ILDCT, CRPDTA.F4111.ILLOCN, CRPDTA.F4111.ILFRTO,
CASE WHEN (CRPDTA.F4111.ilfrto='T' & (crpdta.f4111.ILLOCN='FAB' | crpdta.f4111.ILLOCN='FAB')) THEN Abs([[iltrqt]])
ELSE 0 END
FROM CRPDTA.F4111
WHERE (((CRPDTA.F4111.ILLOCN)='FAB' Or (CRPDTA.F4111.ILLOCN)='RECEIV') AND ((CRPDTA.F4111.ILFRTO)='T') AND ((CRPDTA.F4111.ILMCU)=' SPECTRA'));
Thanks in advance !
Katie
|
|
|
|