Re: If Greater Than Function in SQL ?

aclem_at_ibm.net (Alan Clement) wrote:

>Can anyone tell me if there is a function in SQL to allow
>you to conditionally evaluate whether a columns value is greater
>or less than a second value ?
>
>I am trying to construct a single UPDATE statement to conditionally
>apply a value to a column depending on whether the value is greater
>or less than a given value. That is, I need a function which is a
>variation on DECODE (which only tests for equal values as we know).
>
>ie Rather than
>
>UPDATE table1 SET col1 = DECODE(col1, 'Value1', 'Newval1', 'Newval2')
>
>I need :
>
>UPDATE table1 SET col1 = IF_GR_THAN(col1, 'Value1', 'Newval1', 'Newval2')
 

>If anyone has any bright ideas, apart from perfoming two UPDATEs with different
>WHERE clauses, I am waiting with baited breath...

Alan,

Two suggestions

one:
try writing your own:

create or replace
package compare as
function if_gr_than ( c1 number, v1 number, nv1 number, nv2 number )   return number;
pragma restrict_references( if_gr_than, WNDS, WNPS ); end compare;
/
create or replace
package body compare is
function if_gr_than ( c1 number, v1 number, nv1 number, nv2 number ) return number as
begin
  if c1 > v1 then
    return nv1;
  else
    return nv2;
  end if;
end if_gr_than;
end compare;
/

now you can say:

update <table> set <number column> = compare.if_gr_than(   <number_column>, <val1>, <newval1>, <newval2> );

You can than overload it to accept/return varchar2s, dates, whatever you need.

or two:
If you are only playing with numbers or dates then you can use decode with the sign function.

decode ( sign( a - b ),

   1, new_val1, /* a > b */
  -1, new_val2, /* a < b */
  new_val3 /* a = b */ )

Hope this helps

chris.

--
clbeck_at_us.oracle.com
Oracle Government