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?

CodePudding user response：

With a bit reformulation based on the rules of Aristoteles you may write

• you allways want the end_date is greather than your greatest expression

OR

• in the else case NVL(l_neg_esn_allowed,'N') != 'N' thay may be equal

Predicate

...

AND (
end_date  >  greatest(nvl(l_conv_eff_date, l_contract_vdate), l_contract_vdate)
OR
NVL(l_neg_esn_allowed,'N') != 'N'  and  end_date = greatest(nvl(l_conv_eff_date, l_contract_vdate), l_contract_vdate)
)

which is the same as the predicate below that more resembles your original intention

....
AND (
NVL(l_neg_esn_allowed,'N') = 'N' AND end_date > greatest(nvl(l_conv_eff_date, l_contract_vdate),l_contract_vdate)
OR
NVL(l_neg_esn_allowed,'N') != 'N' AND end_date >= greatest(nvl(l_conv_eff_date, l_contract_vdate),l_contract_vdate)
)

CodePudding user response：

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;

CodePudding user response：

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)