CODEWITHSUNDEEP
oracle-database
user2750658
user2750658

Reputation: 59

decode two values using oracle decode

select status_a,status_b from test

how to decode status_a,status_b below values using oracle decode function aand if one of the values of status_a or status_b is null.

if  status_a='Y' and status_b='Y' then 'Y'

if  status_a='Y' and status_b='N' then 'N'

if  status_a='N' and status_b='Y' then 'N'

if  status_a='N' and status_b='N' then 'N'

Regards,

Chaituhara

Upvotes: 4

Views: 32250

Answers (3)

kishore krv
kishore krv

Reputation: 70

Based on your last comment I guessing ,you are saying that either of value is Y then the result should be Y and also in case either one is null then also it should be Y.

I hope below decode works fine to meet your requirement.

decode(status_a||Status_b,'YY','Y','YN','Y','NY','Y','NN','N','Y')

Correction:

select decode(status_a||status_b,'YY','Y','YN','N','NY','N','NN','N','') as result from test;
--syntax of DECODE: decode(expression, 'match1', 'result1', 'match2', 'result2', 'match3', 'result3', 'matchN', 'resultN', 'defaultIfNoMatch')

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231671

Why do you want to use DECODE? CASE would seem like a much better fit

CASE WHEN status_a = 'Y' and status_b = 'Y' THEN 'Y'
     WHEN status_a = 'Y' and status_b = 'N' THEN 'N'
     WHEN status_a = 'N' and status_b = 'Y' THEN 'N'
     WHEN status_a = 'N' and status_b = 'N' THEN 'N'
  END

Of course, the logic you posted does not appear to make sense. The only way that status_a = 'Y' or status_b = 'Y' would evaluate to FALSE while status_a = 'Y' or status_b = 'N' evaluated to TRUE would be if status_a = 'N' and status_b = 'N'. But that means that the third and fourth branch would never be reached. If you meant and rather than or, the logic would make sense. But in that case, you could simplify it to 

CASE WHEN status_a = 'Y' and status_b = 'Y' THEN 'Y'
     ELSE 'N'
  END

Upvotes: 8

ChrisProsser
ChrisProsser

Reputation: 13088

If you mean AND rather than OR then this can be done using a decode:

decode(status_a,'Y',
       decode(status_b,'Y','Y','N'),
       'N')

or this could be simplified to:

decode(status_a||status_b,'YY','Y','N')

Upvotes: 2

Related Questions