asked on

Case/ If Else for Oracle

Hi All,

I need help defining a piece of SQL that would achieve the following...

Basically I want it to work like this
Case when products = 'Coats' then 'Coats' else 'No Coats'
when product = 'Jackets' then 'Jackets' else 'No Jackets'
when product = 'Hats' then 'Hats' else 'No Hats'

So if there was only coats the list would be

Coats
No Jackets
No Hats

or if there are coats & hats it would read

Coats
Hats
No Jackets

Thanks,
Nici

Guy Hengel [angelIII / a3]

you want something this this, I presume

Case when products = 'Coats' then 'Coats' else 'No Coats' end 
|| ',' case when product = 'Jackets' then 'Jackets' else 'No Jackets' end
|| ',' case when product = 'Hats' then 'Hats' else 'No Hats' end

Open in new window

SOLUTION

sventhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NiciNin

ASKER

Sventhan - Wish I could use this but I can't. The reporting tool I'm using does not support this decode matrix.

Angelll - I have tried your solution but it doesn't work - the multiple cases are causing a syntax errorent

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

NiciNin

ASKER

I really appreciate all help so far and this does run, I wonder is it the best I can expect? Ideally I need them to return like this

1.Jackets
2.Hats
3.No Coats

rather than in one row

Jackets, Hats, no Coats

Is this possible?

Guy Hengel [angelIII / a3]

depends on what display application you are using...

NiciNin

ASKER

I'm using BO webi and it is not possible using this, which is why I wanted to move it back to the DB

Guy Hengel [angelIII / a3]

web... use a "<br>" in between the date items instead of the ",".

ASKER CERTIFIED SOLUTION

awking00

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sventhan

if case works for you then try the this...
select
Case when products = 'Coats' then 'Coats' else 'No Coats' end, -- for coats
case when product = 'Jackets' then 'Jackets' else 'No Jackets' end, -- Jackets
case when product = 'Hats' then 'Hats' else 'No Hats' end --hats
from yourtable

sventhan

I think I gave you another wrong one..
Let me try it again..

NiciNin

ASKER

FYI - I'm using BO XI R2 and none of these solutions will work for this application. Thanks for all the help though

spkirsch

Try this ...

SELECT
'1. ' || Case when products = 'Coats' then 'Coats' else 'No Coats' end
|| chr(10) || '2. ' || case when product = 'Jackets' then 'Jackets' else 'No Jackets' end
|| chr(10) || '3. ' || case when product = 'Hats' then 'Hats' else 'No Hats' end
FROM yourtable