Re: If Greater Than Function in SQL ?

From: Thomas Reichel <treichel_at_telecom.ptt.nl>
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

Original text of this message