Re: If Greater Than Function in SQL ?

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', ..... )

:)

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

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