Home » SQL & PL/SQL » SQL & PL/SQL » get null record with 0 value
get null record with 0 value [message #293698] Mon, 14 January 2008 10:51 Go to next message
rd5241
Messages: 1
Registered: January 2008
Junior Member
i have written following query.

select osc.subcosttypeid, 'Non Management Cap Benifit',
sum(nvl(a.benefitfactorpct * sibd.amount, 0)) amt
from optionssubcosttype osc
left outer join SIBUDGETDETAILS sibd
on sibd.SUBCOSTTYPEID = osc.SUBCOSTTYPEID
left outer join STRATEGICINITIATIVEBUDGETS sib
on sib.budgetid = sibd.budgetid
inner join optionsgovfactor a
on a.COSTTYPEID=sibd.COSTTYPEID
where sib.activityyear = 2007
and sibd.PLANVIEWID = 1
and osc.subcosttypeid=2
and sib.STRATEGICINITIATIVEID = 12730
group by osc.subcosttypeid, osc.SUBCOSTTYPE

i will get

empty record because amount there is no amount for that costtype.

but i need one record with likethis if record count is 0
--> 2,'Non Management Cap Benifit',0
if record count not zero then
-- > 2,'Non Management Cap Benifit',123456

(if amount = 123456)

is there any way simple code i can add to get above 0 record.

i appreciate your help.

Thanks
Ramesh

Re: get null record with 0 value [message #293701 is a reply to message #293698] Mon, 14 January 2008 11:20 Go to previous message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
In this case, use an outer join.

Also, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Always post your Oracle version (4 decimals).

Regards
Michel
Previous Topic: 3 tables
Next Topic: Outer Join with Wildcard
Goto Forum:
  


Current Time: Wed Dec 07 18:13:28 CST 2016

Total time taken to generate the page: 0.27474 seconds