Re: If Greater Than Function in SQL ?
Date: 1995/12/15
Message-ID: <30d1c09f.13351498_at_dcsun4.us.oracle.com>#1/1
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 GovernmentReceived on Fri Dec 15 1995 - 00:00:00 CET