Re: Oracle equivalent to DB2 NULLIF function?

Good point, I just found that out myself the hard way :)

In my instance, I'd prefer that when that happens the default would just be to return mycol - hostVariable, but mine is a specific instance. I'll just have to repeat that expression as the default (when mycol - hostVariable is not zero).

--
Regards,
Jim
Alton Ayers <altona_at_ditw.com> wrote in message news:378F4DEB.7F2F69A9_at_ditw.com...
| 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 - 11:45:59 CDT