• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2676
  • Last Modified:

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
0
NiciNin
Asked:
NiciNin
  • 4
  • 4
  • 3
  • +2
3 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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

0
 
sventhanCommented:
select decode(product,'Coats','Coats','No Coats')  coats,
decode(product,'Jackets' ,'Jackets' , 'No Jackets') jackets,
decode(product = 'Hats' , 'Hats' , 'No Hats' ) hats
from yourtable;
/
 
0
 
NiciNinAuthor Commented:
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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
first of all, sorry, I missed some || in my example.

now, did you put the "end" in the syntax like I showed?


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

0
 
NiciNinAuthor Commented:
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?


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
depends on what display application you are using...
0
 
NiciNinAuthor Commented:
I'm using BO webi and it is not possible using this, which is why I wanted to move it back to the DB
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
web... use a "<br>" in between the date items instead of the ",".
0
 
awking00Commented:
See attached.
product.txt
0
 
sventhanCommented:
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
0
 
sventhanCommented:
I think I gave you another wrong one..
Let me try it again..
0
 
NiciNinAuthor Commented:
FYI - I'm using BO XI R2 and none of these solutions will work for this application. Thanks for all the help though
0
 
spkirschCommented:
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
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 4
  • 4
  • 3
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now