OraFAQ Forum: SQL & PL/SQL » Opposite function to NVL()?

Home » SQL & PL/SQL » SQL & PL/SQL » Opposite function to NVL()?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Opposite function to NVL()? [message #177104]

Tue, 13 June 2006 01:41

zodiac_hh
Messages: 10
Registered: June 2006

Junior Member

Hi there,

is there a build-in standard function in SQL that does the opposite of NVL(), so that it sets a field to NULL if it has a specific value?

Report message to a moderator

Re: Opposite function to NVL()? [message #177105 is a reply to message #177104]

Tue, 13 June 2006 01:49

Barbara Boehmer
Messages: 9106
Registered: November 2002
Location: California, USA

Senior Member

decode (column_name, specific_value, null)

or you could use case

Report message to a moderator

Re: Opposite function to NVL()? [message #177142 is a reply to message #177104]

Tue, 13 June 2006 04:36

zodiac_hh
Messages: 10
Registered: June 2006

Junior Member

works fine, thanks! Can I also assume that there is no predefined function for this?

Report message to a moderator

Re: Opposite function to NVL()? [message #177145 is a reply to message #177142]

Tue, 13 June 2006 04:39

JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland

Senior Member

Yes there is, but Barbara chose to give you a different solution because the pre-defined function is a secret Wink

Report message to a moderator

Re: Opposite function to NVL()? [message #177146 is a reply to message #177142]

Tue, 13 June 2006 04:42

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You are right. You could opt for NVL2. From the manuals:

NVL2 ( expr1 , expr2 , expr3 )

If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any datatype. The arguments expr2 and expr3 can have any datatypes except LONG.

SQL> ed
Wrote file afiedt.buf

  1  select NVL2(NULL
  2             , NULL /* not null becomes null */
  3             , 'your_value_when_null') x
  4* from dual
SQL> /

X
--------------------
your_value_when_null

I think that's not available in PL/SQL but nor is DECODE.

MHE

[Updated on: Tue, 13 June 2006 04:42]

Report message to a moderator