If Greater Than Function in SQL ?

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

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. Received on Fri Dec 15 1995 - 00:00:00 CET