Decode() and Inequalities

Using the Decode() Function
For ">" or "<" Comparisons.

(A) The Oracle Decode() function is not quite as limited as it first seems. The basic usage is to translate one or more values into other specified values. You can specify as many exact {from-to} translation pairs as needed, followed by a default value if none of the {from} values match.

   
   with w_sample_data as
     ( select  NULL as id,  NULL as nm
         from  dual  where 1=2
       -----------------------------------
       union all select 1, 'Bob' from dual
       union all select 2, 'Jan' from dual
       union all select 3, 'Sam' from dual
     )
   select  id, nm
        ,  decode( nm, 'Ben', 900
                     , 'Sam', 901
                     , 'Jan', 902, 0 )  as rslt
     from  w_sample_data ;

(B) The CASE statement allows for more complex comparisons.

   
   with w_sample_data as
     ( select  NULL as id,  NULL as nm,  NULL as age
         from  dual  where 1=2
       ---------------------------------------
       union all select 1, 'Bob', 65 from dual
       union all select 2, 'Jan', 42 from dual
       union all select 3, 'Sam', 14 from dual
       union all select 4, 'Ron', 66 from dual
     )
   select  id, nm, age
        ,  case
             when age > 65  then 'Group-3'
             when age between 18 and 65  then 'Group-2'
             else 'Group-1'
           end  as rslt
     from  w_sample_data ;

(C) Either the Greatest() or the Least() function can be used with the Decode() function to correctly evaluate a ‘Greater-Than’ operator. Notice that the parameters are slightly different in the two different implementations, and that either one gives the same result as the CASE construct.

  With  w_data  as
    ( select  NULL  as id
           ,  NULL  as val
           ,  NULL  as tst
        from  dual  where 1=2
     ----------------------------------------------
      union all select  'a',  3.9   , 4  from dual
      union all select  'b',  3.999 , 4  from dual
      union all select  'c',  4.0   , 4  from dual
      union all select  'd',  4.001 , 4  from dual
      union all select  'e',  7.8   , 4  from dual
     ----------------------------------------------
    )
  select  id, val, tst
       ,  case when val > tst then 'True' else 'f' end   as gt_case
       ,  decode( greatest(val, tst), tst, 'f', 'True' ) as gt_decode_greatest
       ,  decode(    least(val, tst), val, 'f', 'True' ) as gt_decode_least
    from  w_data
   order  by 1 ;

(D) All of the inequality operators can be replecated in the Decode() function using either the Greatest() or the Least() function ( ">", ">=", "<", "<=" ). Notice the example below shows the different paramenter configurations to replicate the CASE construct using the Decode() with either the Greatest() or Least() functions.

   With  w_data  as
     ( select  NULL  as id
            ,  NULL  as val
            ,  NULL  as tst
         from  dual  where 1=2
      ----------------------------------------------
       union all select  'a',  3.9   , 4  from dual
       union all select  'b',  3.999 , 4  from dual
       union all select  'c',  4.0   , 4  from dual
       union all select  'd',  4.001 , 4  from dual
       union all select  'e',  7.8   , 4  from dual
      ----------------------------------------------
     )
   select  id, val, tst
       --  ----------
        ,  case when val >  tst then 'True' else 'f' end  as c_gt
        ,  case when val >= tst then 'True' else 'f' end  as c_gte
        ,  case when val <  tst then 'True' else 'f' end  as c_lt
        ,  case when val <= tst then 'True' else 'f' end  as c_lte
       --  ----------
        ,  decode(greatest(val, tst), tst, 'f', 'True')   as gst_gt
        ,  decode(greatest(val, tst), val, 'True', 'f')   as gst_gte
        ,  decode(greatest(val, tst), val, 'f', 'True')   as gst_lt
        ,  decode(greatest(val, tst), tst, 'True', 'f')   as gst_lte
       --  ----------
        ,  decode(   least(val, tst), val, 'f', 'True')   as lst_gt
        ,  decode(   least(val, tst), tst, 'True', 'f')   as lst_gte
        ,  decode(   least(val, tst), tst, 'f', 'True')   as lst_lt
        ,  decode(   least(val, tst), val, 'True', 'f')   as lst_lte
       --  ----------
     from  w_data
    order  by 1 ;

(E) Here's a quick summary of how to use the decode() function to evaluate inequalities.

  Quick Summary

    a >  b ,   Greater-Than:

      Decode( Greatest(a, b), b, { rslt if NOT >  }, { rslt if is  >  } )
      Decode(    Least(a, b), a, { rslt if NOT >  }, { rslt if is  >  } )

    a >= b ,   Greater-Than-or-Equal-to:

      Decode( Greatest(a, b), a, { rslt if is  >= }, { rslt if NOT >= } )
      Decode(    Least(a, b), b, { rslt if is  >= }, { rslt if NOT >= } )

    a <  b ,   Less-Than:

      Decode( Greatest(a, b), a, { rslt if NOT <  }, { rslt if is  <  } )
      Decode(    Least(a, b), b, { rslt if NOT <  }, { rslt if is  <  } )

    a <= b ,   Less-Than-or-Equal-to:

      Decode( Greatest(a, b), b, { rslt if is  <= }, { rslt if NOT <= } )
      Decode(    Least(a, b), a, { rslt if is  <= }, { rslt if NOT <= } )

2019-Mar-12 Steve Lyon