Home » SQL & PL/SQL » SQL & PL/SQL » NVL Function (Oracle 10g)
NVL Function [message #569313] Wed, 24 October 2012 11:39 Go to next message
akull
Messages: 39
Registered: July 2012
Location: Argentina
Member
Good Evening to everyone.

I'm currently facing a problem making a Query in Oracle, the problem I have is, the user of the application send regulary an amount of cases to be solved from the DB, but in case that the case doesn't exist we must let'em know.
So, to try to to this I'm aplying the NVL function witch retrieves the parameter if the value is null, but it doesn't work because the record doesn't exist, in PL/SQL could be a NO_DATA_FOUND. Is there any built-in function wich allow me to do that ?
Re: NVL Function [message #569314 is a reply to message #569313] Wed, 24 October 2012 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 58636
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a way (that can be adapt to any language and client tool):
SQL> host type m.sql
set feedback off
col f new_value flag
def flag=0
select dummy, 1 f from dual where 1=&&value;
set head off
select '&&value does not exist!' from dual where &flag=0;
set head on
undef flag
undef value

SQL> @.\m.sql
Enter value for value: 1
D          F
- ----------
X          1
SQL> @.\m.sql
Enter value for value: 0
0 does not exist!

Regards
Michel
Re: NVL Function [message #569327 is a reply to message #569314] Wed, 24 October 2012 15:11 Go to previous message
Bill B
Messages: 1068
Registered: December 2004
Senior Member
The following will work


>select nvl(max(dummy),0)
  2  from dual;

N
-
X

>edit
Wrote file afiedt.buf

  1  select nvl(max(dummy),0)
  2  from dual
  3* where 1=2
>/

N
-
0

Previous Topic: To_Date()
Next Topic: Procedure Doubt
Goto Forum:
  


Current Time: Thu Jul 31 15:08:32 CDT 2014

Total time taken to generate the page: 0.12568 seconds