Home » SQL & PL/SQL » SQL & PL/SQL » Convert no rows returned to zero (Oracle 91 & 10g)
Convert no rows returned to zero [message #382602] Fri, 23 January 2009 03:34 Go to next message
fakru.y
Messages: 34
Registered: May 2007
Member
My select query is returning no rows.
i want convet one to the selected column as ZERO(0).

How we can perform this?

Thanks in advance.

[Updated on: Fri, 23 January 2009 03:42]

Report message to a moderator

Re: Convert no rows returned to zero [message #382609 is a reply to message #382602] Fri, 23 January 2009 03:56 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
By

SELECT COUNT (*)
          FROM DUAL
         WHERE 1 = 2


Thanks
Trivendra
Re: Convert no rows returned to zero [message #382616 is a reply to message #382609] Fri, 23 January 2009 04:11 Go to previous messageGo to next message
fakru.y
Messages: 34
Registered: May 2007
Member
That's right but my scenario is like this
Subquery is returning no rows.
But in main query the column which is refering from subquery should return as 0

select e.b from(
(select a,b from c where a=5) e)

here subquery select a,b from c where a=5) e is retuning no rows
my query should returns 0.
Re: Convert no rows returned to zero [message #382621 is a reply to message #382616] Fri, 23 January 2009 04:22 Go to previous messageGo to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
I am not sure why would you want to return zero. You may rewrite code something like this

select e.b from(
                select a,b from c where a=5) e
union all
select 0 from dual where not exists (select a,b from c where a=5)


Now I am assuming b is of numeric datatype


[Updated on: Fri, 23 January 2009 04:41]

Report message to a moderator

Re: Convert no rows returned to zero [message #382647 is a reply to message #382616] Fri, 23 January 2009 07:07 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Outer join e with dual and use nvl to translate null to zero
Re: Convert no rows returned to zero [message #390868 is a reply to message #382616] Tue, 10 March 2009 02:13 Go to previous messageGo to next message
_jum
Messages: 509
Registered: February 2008
Senior Member
the following construct returns b if the ist condition is true and 0 if it is false and gives no rows.
select nvl(min(e.b),0) from
( (select 2 b from dual where 1=3) e)

select nvl(min(e.b),0) from
( (select 2 b from dual where 1=1) e)
Re: Convert no rows returned to zero [message #420118 is a reply to message #382602] Thu, 27 August 2009 02:43 Go to previous messageGo to next message
mart
Messages: 3
Registered: August 2009
Junior Member

Do you get the solution of your answer ,if then eay what is the answer.
Thanks

martyna

[Updated on: Thu, 27 August 2009 05:54] by Moderator

Report message to a moderator

Re: Convert no rows returned to zero [message #420158 is a reply to message #420118] Thu, 27 August 2009 05:55 Go to previous message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Aren't the current replies an answer?

Regards
Michel
Previous Topic: Displaying Current Date records
Next Topic: trigger
Goto Forum:
  


Current Time: Fri Dec 09 21:24:12 CST 2016

Total time taken to generate the page: 0.06114 seconds