You colud try joining to an in-line view something like SELECT :XDATE+(ROWNUM-1) DDATE FROM DBA_OBJECTS WHERE ROWNUM <= (:YDATE - :xdate)+1
where dba_objects could be any table with enough rows to ensure you always covered the complete range. -----Original Message----- Aidan Whitehall Sent: 29 October 2003 10:49 To: Multiple recipients of list ORACLE-L This is probably a no-brainer... We have some date-based data for which most days have several records but where some days have none. I'm COUNT()ing the number of records for each day (between day x and day y) and need a record set that also includes a row for those days which have no records: UkDate Total 1/1/2003 5 2/1/2003 6 3/1/2003 0 4/1/2003 6 I could post-process the record set to achieve this, but is there any way in 9i to do an aggregate query with an outer join on a date range (if that makes sense)? Someone made the suggestion of creating another table with a row for every day under the sun in it, against which you could inner join the main query, but I'm not keen on that (that is just a gut response though). Any ideas? Thanks! -- Aidan Whitehall <mailto:[EMAIL PROTECTED]> Macromedia ColdFusion Developer Fairbanks Environmental Ltd +44 (0)1695 51775 Queen's Awards Winner 2003 <http://www.fairbanks.co.uk/go/awards> ________________________________________________________________________ This e-mail has been scanned for all viruses by Star Internet. The service is powered by MessageLabs. For more information on a proactive anti-virus service working around the clock, around the globe, visit: http://www.star.net.uk ________________________________________________________________________ -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Aidan Whitehall INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nicoll, Iain INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
