How do i get this CASE statement right in PLSQL?

Question

I'm getting ORA- 00936 missing expression error near > symbol while trying to run this request :

SELECT contract_ref_no,
       component
FROM   some_table
WHERE  Contract_ref_no = '123'
AND    component = 'ABC'
AND end_date
   (CASE WHEN NVL(l_neg_esn_allowed,'N') = 'N' 
    THEN 
       > greatest(nvl(l_conv_eff_date, l_contract_vdate),
                l_contract_vdate)
    ELSE                 
       >=
     greatest(nvl(l_conv_eff_date, l_contract_vdate),
                         l_contract_vdate)  
    END) 

How can I fix it?

Answer 1

You can change your case statement to -

SELECT contract_ref_no,
       component
  FROM some_table
 WHERE Contract_ref_no = '123'
   AND component = 'ABC'
   AND CASE WHEN NVL(l_neg_esn_allowed,'N') = 'N'
                 AND end_date > greatest(nvl(l_conv_eff_date, l_contract_vdate),
                                         l_contract_vdate) THEN
                 1
            WHEN end_date >= greatest(nvl(l_conv_eff_date, l_contract_vdate),
                                      l_contract_vdate) THEN
                 1
       END = 1;

Answer 2

The SQL query that you write have syntax error.

The THEN > something can't work : Superior to what ? Same with the >= after, superior or equals to what ?

So, you should have something like column > value, such as you will have column = value or column LIKE 'my val'.

Finally, I suggest you to use this code:

SELECT contract_ref_no,
       component
FROM   some_table
WHERE  Contract_ref_no = '123'
AND    component = 'ABC'
AND end_date
   (CASE WHEN NVL(l_neg_esn_allowed,'N') = 'N' 
    THEN some_date > greatest(nvl(l_conv_eff_date, l_contract_vdate),
                l_contract_vdate)
    ELSE some_date >= greatest(nvl(l_conv_eff_date, l_contract_vdate),
                         l_contract_vdate)  
    END)