Q
Problem solve Get help with specific problems with your technologies, process and projects.

Trying to list all dates, even though count is zero

I have a select statment like below:

 
 SELECT count(r.REQUEST) request,to_char(r.DATEreq,
 'dd.mm.yyyy') as
 REQUESTDATE 
 FROM REQUESTpool r 
 WHERE (r.REQUESTDATE >= to_date('01.07.2002
 14:42','dd.mm.yyyy hh24:mi')) 
 AND (r.REQUESTDATE <= to_date('07.07.2002
 14:42','dd.mm.yyyy hh24:mi')) 
 AND lower(r.RECIPIENT) like
 lower('%00491633732648%') 
 GROUP BY to_char(r.REQUESTDATE, 'dd.mm.yyyy') 
 ORDER BY to_char(r.REQUESTDATE, 'dd.mm.yyyy') 
Here the output I get is:
  
 request requestdate 
 ----------------------- 
 1 02.07.2002 
 4 03.07.2002 
 2 06.07.2002 
 
I want a different output. I want the dates to be displayed from 01.07.2002 to 07.07.2002, even though the count is 0, like below:
 
 request requestdate 
 ----------------------- 
 0 01.07.2002 
 1 02.07.2002 
 4 03.07.2002 
 0 04.07.2002 
 0 05.07.2002 
 2 06.07.2002 
 0 07.07.2002 
 
Can you tell me the correct SQL statement for this?

Unfortunately, you are asking for information from one table, and then asking for the system to supply information that doesn't exist in that table. The system simply won't do this. But you can trick the system into doing what you want by introducing a dummy data source. Try the following query:

SELECT count(r.REQUEST) request,to_char(r.DATEreq,
'dd.mm.yyyy') as REQUESTDATE 
FROM REQUESTpool r 
WHERE (r.REQUESTDATE >= to_date('01.07.2002
14:42','dd.mm.yyyy hh24:mi')) 
AND (r.REQUESTDATE <= to_date('07.07.2002
14:42','dd.mm.yyyy hh24:mi')) 
AND lower(r.RECIPIENT) like lower('%00491633732648%') 
GROUP BY to_char(r.REQUESTDATE, 'dd.mm.yyyy') 
UNION
SELECT
'0',TO_CHAR(TO_DATE('01.06.2002','dd.mm.yyyy')+ROWNUM,'dd.mm.yyyy')
as REQUESTDATE 
FROM dba_objects
WHERE ROWNUM <= 7;
The trick here is to add an entry for every date that has a count of '0'. Now that you have this, you can add to your existing entries and you'll have all days in your range. Then you can sort.

For More Information


This was last published in July 2002

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close