Re: If Greater Than Function in SQL ?

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 ?

[snip]

>I need :
>
>UPDATE table1 SET col1 = IF_GR_THAN(col1, 'Value1', 'Newval1', 'Newval2')
>
>In Microrim's R:Base RDMS product there is indeed functions called
>IFEQ(), IFGT(), IFLT() which test for values being equal, greater,
>or less than the given parameter. But I cannot find an Oracle equivelant.
>A deficiency in Oracle's Language.....SURELY NOT !!
>
>If anyone has any bright ideas, apart from perfoming two UPDATEs with different
>WHERE clauses, I am waiting with baited breath...
>
>Cheers Alan.

Is this for numeric values? If so, subtract the value from the column's value and the SIGN function. DECODE that result. The SIGN function will return 1 if the arguments is positive, -1 if negative, or 0 if it is equal to zero.

SIGN(.65) = 1
SIGN( 0 ) = 0
SIGN(-7 ) = -1

Instead of
UPDATE table1 SET col1 = IF_GR_THAN(col1, 'Value1', 'Newval1', 'Newval2')

use
UPDATE table1
SET col1 = DECODE( SIGN( col1-Value1 ), 1, 'Newval1', 'Newval2' ))

If you have version 7.2, you can create a stored function called IF_GR_THAN, and use it in your SQL statements. At least I think it's available starting in 7.2. If not, I'm sure someone will correct me. Received on Mon Dec 18 1995 - 00:00:00 CET