Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Simple SQL update

Re: Simple SQL update

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: 13 Aug 2006 06:32:16 -0700
Message-ID: <1155475936.150747.240160@i3g2000cwc.googlegroups.com>


Randy Harris wrote:
> I'm trying to do an update in Oracle 9i that seems like it should be
> very simple but I can't figure out the syntax. The update needs to use
> a different formula Dependant on the current value of the attribute.
>
> Simplified:
>
> UPDATE MyTable Set F1 = (F1 > 1) * 2 + 1 ;
>
> This returns a missing right parenthesis error.
>
> As does
>
> Select (2>1) FROM dual ;
>
> How do I put the comparison into the calculation?
>
> Randy Harris
> tech at promail dot com

Try an experiment using SIGN - the following determines if 2 (or a column name if you replace 2 with the column name) is greater than 1 SELECT
  DECODE(SIGN(2-1), 1, 'GREATER THAN 1', 0, 'EQUAL TO 1', -1, 'LESS THAN 1')
FROM
  DUAL; If that gives you what you need, try something like this: SELECT
  DECODE(SIGN(F1 - 1), 1, F1 * 2 + 1, 0, 1, -1, 1) FROM
  MYTABLE; If that give you what you need, you can further shorten the syntax: SELECT
  DECODE(SIGN(F1 - 1), 1, F1 * 2 + 1, 1) FROM
  MYTABLE; The above can be thought of as follows:
If (F1 - 1) is greater than 0 (logically equivalent to F1 is greater than 1), then return F1 * 2 + 1
else return 1

SIGN returns 1 if the value being evaluated is greater than 0, 0 if the value being evaluated is 0, and -1 if the value being evaluated is less than 0.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sun Aug 13 2006 - 08:32:16 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US