OraFAQ Forum: SQL & PL/SQL » How to return 0 when Inline View is returning NULL

Home » SQL & PL/SQL » SQL & PL/SQL » How to return 0 when Inline View is returning NULL (Oracle - 11.1.0.7.0)

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

How to return 0 when Inline View is returning NULL [message #565666]

Thu, 06 September 2012 01:40

Akash Nathile
Messages: 38
Registered: October 2006
Location: Pune

Member

Hi ALL,

Attached query is running fine if inline view B (Marked in Comments) returning value. If inline view B returns NULL then it fails to return result.

I want if Inline view B is returning NULL then it should pass ZERO to main query.

Regards,
Akash

Attachment: SQL.txt
(Size: 1.05KB, Downloaded 1037 times)

[Updated on: Thu, 06 September 2012 01:44]

Report message to a moderator

Re: How to return 0 when Inline View is returning NULL [message #565668 is a reply to message #565666]

Thu, 06 September 2012 01:47

bhat.veeresh@gmail.com
Messages: 88
Registered: July 2012
Location: Bangalore

Member

SQL> select nvl((select null from dual),0) from dual;

NVL((SELECTNULLFROMDUAL),0)
---------------------------
                          0
SQL> select nvl((select 1 from dual),0) from dual;

NVL((SELECT1FROMDUAL),0)
------------------------
                       1

Report message to a moderator

Re: How to return 0 when Inline View is returning NULL [message #565669 is a reply to message #565666]

Thu, 06 September 2012 01:52

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

All Oracle SQL functions are described in Database SQL Reference.
It is worth to at least just have a look at it and the purpose of each function.
Concerning NULL, in addition to NBL, you also have NVL2, NULLIF, COALESCE functions.

Regards
Michel

[Updated on: Thu, 10 April 2014 00:57]

Report message to a moderator

Re: How to return 0 when Inline View is returning NULL [message #611896 is a reply to message #565668]

Wed, 09 April 2014 17:19

ankursharma1111
Messages: 1
Registered: June 2013

Junior Member

Pls tell how to do this(return 0) with inline views

Report message to a moderator

Re: How to return 0 when Inline View is returning NULL [message #611898 is a reply to message #611896]

Wed, 09 April 2014 17:37

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and please read http://www.orafaq.com/forum/t/174502/102589/

Report message to a moderator

Re: How to return 0 when Inline View is returning NULL [message #611913 is a reply to message #611896]

Thu, 10 April 2014 02:24

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

ankursharma1111 wrote on Wed, 09 April 2014 23:19

Pls tell how to do this(return 0) with inline views

Have a long hard look at the 2nd post in this thread.

Report message to a moderator

Re: How to return 0 when Inline View is returning NULL [message #611915 is a reply to message #611913]

Thu, 10 April 2014 02:33

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I think (although I'm not sure) he meant having the inline view in FROM part instead of SELECT one; something like:

SQL> select nvl(b.v, a.v) res 
  2  from (select 0 v from dual) a, (select null v from dual) b
  3  /
       RES
----------
         0

1 row selected.

SQL> select nvl(b.v, a.v) res
  2  from (select 0 v from dual) a, (select 1 v from dual) b
  3  /
       RES
----------
         1

1 row selected.

Report message to a moderator

Re: How to return 0 when Inline View is returning NULL [message #611916 is a reply to message #611915]

Thu, 10 April 2014 02:35

Michel Cadot
Messages: 68643
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Or:

SQL> select nvl(b.v, 0) res 
  2  from dual a left outer join (select 1 v from dual where 1=0) b on 1=1
  3  /
       RES
----------
         0

1 row selected.

SQL> select nvl(b.v, 0) res 
  2  from dual a left outer join (select 1 v from dual where 1=1) b on 1=1
  3  /
       RES
----------
         1

1 row selected.

Report message to a moderator

Re: How to return 0 when Inline View is returning NULL [message #611917 is a reply to message #611915]

Thu, 10 April 2014 02:36

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

True, but you're still using the same functions.

Report message to a moderator

Previous Topic:	CONCATENATE QUERY
Next Topic:	"Where 1=2 " . How it works internally?

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 19:17:33 CDT 2024