Re: If Greater Than Function in SQL ?
Date: 1995/12/19
Message-ID: <4b67ta$pr4_at_hdxx05.telecom.ptt.nl>#1/1
In article <4asomb$328_at_inet-nntp-gw-1.us.oracle.com>, tkyte_at_us.oracle.com
says...
>
>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', 'New
>val2')
>
>>>>If anyone has any bright ideas, apart from perfoming two UPDATEs wi
th 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
>
It is also possible to use the SIGN-function:
DECODE
(SIGN (value1 - value2 ,1,'1 is greater' ,0,'equal' ,-1,2 is greater' ) )Received on Tue Dec 19 1995 - 00:00:00 CET