Home » SQL & PL/SQL » SQL & PL/SQL » Forcing SQL to show none existent date entries (Oracle 9i XP)
Forcing SQL to show none existent date entries [message #427410] Thu, 22 October 2009 08:25 Go to next message
thabo
Messages: 8
Registered: October 2009
Location: South Africa
Junior Member
please help me or point me in the right direction, i have a script as shown below
SELECT SDATE, nvl(FAILURES,0) FAILURES
FROM 
(SELECT  TRUNC (start_time, 'hh') sdate, nvl(COUNT (*),0) failures
FROM pinless
WHERE status = 'ACC'
GROUP BY TRUNC (start_time, 'hh'))
order by sdate desc


results are something like this:-
Quote:

SDATE FAILURES
22/10/2009 13:00:00 1
22/10/2009 11:00:00 1
22/10/2009 10:00:00 4
22/10/2009 09:00:00 3


i dont have entries for 12:00:00 in the table but i wish for my script to show 12:00:00 and zero failues like this:-
SDATE FAILURES
22/10/2009 13:00:00 1
22/10/2009 12:00:00 0
22/10/2009 11:00:00 1
22/10/2009 10:00:00 4
22/10/2009 09:00:00 3
[/quote]

this should apply for all hours where i dont have any entries, is this possible?



Re: Forcing SQL to show none existent date entries [message #427416 is a reply to message #427410] Thu, 22 October 2009 08:54 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You have to outer join your query with a "calendar" containing all the hours you want to display.

Use code tags ALSO for results and align the columns.

Regards
Michel
Previous Topic: Find Nth row inside an UPDATE
Next Topic: write messages from pl/sql
Goto Forum:
  


Current Time: Tue Dec 06 12:25:23 CST 2016

Total time taken to generate the page: 0.14350 seconds