Home » SQL & PL/SQL » SQL & PL/SQL » Result If A Subquery Returns No Data (Oracle 10.2.0.1)
icon5.gif  Result If A Subquery Returns No Data [message #445205] Fri, 26 February 2010 07:28 Go to next message
felipe.vinturini
Messages: 5
Registered: February 2010
Location: Brasil
Junior Member
Hi All,

I have a query that can return values or not:
If it returns a value, I want to print the value returned;
If it returns no value, I would like to return a string like 'NO_DATA'.

I have already tried several things, and the last one is:
==================================================================
Select NVL2(dnh.DiaNoHableData, dnh.DiaNoHableData, 'NO_DATA')
From (Select Dia_No_Habil || '#' ||
Trim(Descripcion) DiaNoHableData
From Dias_no_Habiles
Where Dia_No_Habil = To_Char(To_Date('20080704', 'YYYYMMDD'), 'YYYYMMDD')) dnh;
==================================================================

The subquery returns no data, so the first query should print 'NO_DATA', but it returns nothing.

Let me know if my question is not clear and if you need further details!

Thanks & Regards!
Re: Result If A Subquery Returns No Data [message #445211 is a reply to message #445205] Fri, 26 February 2010 07:44 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
SQL>  select nvl(data,'NO_data') from (select ename data from emp where 1=2);

no rows selected

SQL> select nvl(data,'NO_data') from (select null data from dual);

NVL(DAT
-------
NO_data

SQL>


if you know how Oracle executes these statements then you can easily find the solution...

for more reference see pl/sql documents for
exception
when no_data_found then


sriram Smile

[Updated on: Fri, 26 February 2010 07:45]

Report message to a moderator

Re: Result If A Subquery Returns No Data [message #445217 is a reply to message #445205] Fri, 26 February 2010 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> create table t (id integer, col varchar2(10));

Table created.

SQL> insert into t values(1,'Some Data');

1 row created.

SQL> select nvl( (select col from t where id=1)
  2            , 'No Data') val
  3  from dual
  4  /
VAL
----------
Some Data

1 row selected.

SQL> select nvl( (select col from t where id=2)
  2            , 'No Data') val
  3  from dual
  4  /
VAL
----------
No Data

1 row selected.

Regards
Michel
icon12.gif  Re: Result If A Subquery Returns No Data [message #445218 is a reply to message #445217] Fri, 26 February 2010 08:47 Go to previous message
felipe.vinturini
Messages: 5
Registered: February 2010
Location: Brasil
Junior Member
Hi All,

Thanks for the prompt reply!

I hope so!

Regards!
Previous Topic: Query rewrite
Next Topic: Complex pivot query - help
Goto Forum:
  


Current Time: Sun Dec 04 02:55:30 CST 2016

Total time taken to generate the page: 0.12964 seconds