CODEWITHSUNDEEP
sqloracle-databaseselectcase-when
Marcus
Marcus

Reputation: 3869

CASE..WHEN condition not working in Oracle Sql

I have below case when condition in query which is not getting somehow satisfied.

I want to write logic in case when in select query such that when LAST_TRADEABLE_DT is not null then take LAST_TRADEABLE_DT, when LAST_TRADEABLE_DT is null then the below condition should satisfied:

WHEN t.opt_exer_typ = 'American'
            AND  t.mic_exch_code = 'XDMI'
            THEN t.opt_expire_dt - 1
            WHEN t.opt_exer_typ != 'American' 
            THEN t.opt_expire_dt

I have written below query but i am getting last_tradedate as null. I never want last_tradedate to return null.

  SELECT
    t.opt_exer_typ,
    t.mic_exch_code,
    t.ID_BB_GLOBAL,    
    t.LAST_TRADEABLE_DT,    
    t.OPT_EXER_TYP,
    (
        CASE 
            WHEN t.LAST_TRADEABLE_DT is not null  
            THEN t.LAST_TRADEABLE_DT                     
            WHEN t.LAST_TRADEABLE_DT IS NULL 
            AND  t.opt_exer_typ = 'American'
            AND  t.mic_exch_code = 'XDMI'
            THEN t.opt_expire_dt - 1
            WHEN t.LAST_TRADEABLE_DT IS NULL 
            AND  t.opt_exer_typ != 'American' 
            THEN t.opt_expire_dt  
        END) last_tradedate,
    t.OPT_EXPIRE_DT
FROM
    TEST_AGG t where t.ID_BB_GLOBAL = 'XXX1234';

Upvotes: 0

Views: 163

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269753

If you don't want the value ever to be NULL, then use ELSE. I presume you want:

 (CASE WHEN t.LAST_TRADEABLE_DT is not null  
       THEN t.LAST_TRADEABLE_DT                     
       WHEN t.opt_exer_typ = 'American' AND
            t.mic_exch_code = 'XDMI'
       THEN t.opt_expire_dt - 1
       ELSE t.opt_expire_dt  
  END) last_tradedate,

I'm not 100% sure that is the logic you want. Your code will return NULL value in the following situations:

  • opt_exer_typ = 'American' and mic_exch_code <> XDMI
  • opt_exer_typ = 'American' and mic_exch_code IS NULL
  • opt_exer_typ IS NULL

(assuming the first condition is not met).

Upvotes: 1

Related Questions