Re: Oracle equivalent to DB2 NULLIF function?

Jim,

You'll also need an additional parameter (default) for when "mycol - hostVariable" isn't zero.

UPDATE mytable set mycol = decode(mycol - hostVariable, 0, NULL,mycol - hostVariable);

• Alton

Jim Morgan wrote:

> Yep, decode did the trick! This is the statement I am using:
>
> UPDATE mytable set mycol = decode(mycol - hostVariable, 0, NULL);
>
> Now mycol will be set to NULL if mycol - hostVariable evaluates to zero.
> Thanks!
>
> --
> Regards,
> Jim
> Jim Morgan <jjmorgan_at_us.ibm.com> wrote in message
> news:7mna01$kg0$1_at_news.btv.ibm.com...
> | The NVL function in Oracle is equivalent to the COALESCE function in DB2.
> | NULLIF kind of does the opposite, returns a NULL if an expression
> evaluates
> | to true. Let me tell you exactly what I am trying to do:
> |
> | UPDATE mytable SET mycol = mycol - hostVariable;
> |
> | What I want to do is automatically set mycol to NULL if the new value of
> | mycol ends up being zero. I'll check out the DECODE function, thanks for
> | the suggestion.
> |
> | --
> | Regards,
> | Jim
> | Andy Hardy <aph_at_ahardy.demon.co.uk> wrote in message
> | news:Yvnk5GApeuj3Ewca_at_ahardy.demon.co.uk...
> | | In article <7mm0pa$esm$1_at_news.btv.ibm.com>, Jim Morgan
> | | <jjmorgan_at_us.ibm.com> writes
> | | >Anyone know if there is an Oracle equivalent to this DB2 function?
> | Thanks!
> | |
> | | What does NULLIF do?
> | |
> | | If it's a 'return null id its this value' then perhaps DECODE would
> | | suffice? e.g. DECODE (my_column, 'shouldbenull', NULL, my_column).
> | |
> | | If it's a 'return this value instead of a null' then try NVL e.g.
> | | NVL(my_column, 'it was a null value').
> | |
> | | Otherwise...
> | |
> | | Andy
> | | --
> | | Andy Hardy. PGP key available on request
> | | ===============================================================
> |
> |
Received on Fri Jul 16 1999 - 10:21:15 CDT