Re: If Greater Than Function in SQL ?

tkyte_at_us.oracle.com (Thomas J Kyte) wrote:

>clbeck_at_us.oracle.com (Christopher L. Beck) wrote:
 

>>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:
>[valid suggestions snipped]
 

>Or you could just use greatest.
 

>update table1 set col1 = greatest( col1, 'value1', 'newvalue1', ..... )

opps:

meant to say

update table1 set col1 = decode( greatest(col1,'value1'),

                                    col1, newval1, 
                                'value1', 'newval2' )

if col1 and 'value1' are equal, the newval1 will be returned.

>:)

>>chris.
>>--
>>clbeck_at_us.oracle.com
>>Oracle Government
 

>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Sat Dec 16 1995 - 00:00:00 CET