Search code examples
sqlms-accesscaseiif

Convert Access IIF Statement to Oracle Case Statement

Would anyone know how to convert a Access SQL IIF Statement to a Oracle/SSMS CASE Statement? Thank you in advance. Please see expression below

 (Type NOT IN ('Swap') AND Mat_Date <= Eff_Date) 
 OR (Type = 'SWAP' AND iif(First_Date_pay < First_Date_rec, First_Date_pay, 
 First_Date_rec) < DateAdd("d", 150, Trd_Date) AND Mat_Date <= Eff_Date)
Solution

  • Don't use case of iif() in a where clause. Instead, just use regular boolean logic. In your case, you just need the least() function (assuming the values are not NULL):

    where (Type not in ('Swap') and Mat_Date <= Eff_Date
      ) or
      (Type = 'SWAP' and
       least(First_Date_pay, First_Date_rec) < Trd_Date + interval '150' day and
      Mat_Date <= Eff_Date
     )