CODEWITHSUNDEEP
sqloracle-databasedecode
flyingbird013
flyingbird013

Reputation: 476

Oracle : Decode for more than one key field

I have table 

Date_pkp  | date_pkp_delete | status
<null>    |  <null>         |
10.10.2000| 20.11.2009      |
12.10.2005| <null>          |

If 'Date_pkp' is null AND 'date_pkp_delete' is null then 'status' = Non PKP

If 'Date_pkp' is not null AND 'date_pkp_delete' is not null, then 'status' = Non PKP

If 'Date_pkp' is not null AND 'date_pkp_delete' is null, then 'status' = PKP

How to query with decode?

SELECT date_pkp, date_pkp_delete, Decode(...) status from table

Upvotes: 1

Views: 348

Answers (2)

San
San

Reputation: 4538

Since you have to check multiple conditions, instead of writing complected decode, use case statement as follows:

select DATE_PKP, DATE_PKP_DELETE, case when (DATE_PKP is null and DATE_PKP_DELETE is null) then  'Non PKP'
                                       when (DATE_PKP is not null and DATE_PKP_DELETE is not null) then 'Non PKP'
                                       when (DATE_PKP is not null and DATE_PKP_DELETE is null) then 'PKP status' 
                                   end STATUS
from table;

Upvotes: 1

Mureinik
Mureinik

Reputation: 311393

It would be easier to do this with a case expression:

SELECT date_pkp,
       date_pkp_delete
       CASE WHEN ((date_pkp IS NULL AND date_pkp_delete IS NULL) OR
                  (date_pkp IS NOT NULL AND date_pkp_delete IS NOT NULL))
            THEN 'Non PKP'
            WHEN (date_pkp IS NOT NULL AND date_pkp_delete IS NULL)
            THEN 'PKP'
       END AS status
FROM   my_table

Upvotes: 1

Related Questions