| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Date-based query Q
Hello Aidan,
I ran into a similar situation once, except that I needed a row not for every day, but for every month. My solution at that time was, in fact, to create a table with a row for each month for the next hundred years (only 1200 rows). I also wrote (and documented) a small program to extend that table when necessary, though I seriously doubt anyone will ever need to run that program<grin>.
More to the point, I wrote an article about that problem awhile back, which you can read at:
http://otn.oracle.com/oramag/oracle/02-sep/o52sql.html
The solution in my article is similar to, but not quite the same as, the solution I actually implemented. The article solution is a bit more generic. At the end of the article I show another solution based on a table function that you might be interested in, because that solution does not call for the creation of a real table with rows for each day, and you said you didn't want to actually create such a table.
By the way, three's a cool, new, partition outer-join feature in Oracle Database 10g that enables you to write outer-joins such as I did in my article much more easily. It simplifies syntax, though it doesn't really add any functionality. I'm thinking of writing about it for my next Oracle-article list article.
Best regards,
Jonathan Gennick --- Brighten the corner where you are http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article, or send email to Oracle-article-request_at_gennick.com and include the word "subscribe" in either the subject or body.
Wednesday, October 29, 2003, 5:49:26 AM, you wrote: AW> This is probably a no-brainer...
AW> We have some date-based data for which most days have several records AW> but where some days have none. I'm COUNT()ing the number of records for AW> each day (between day x and day y) and need a record set that also AW> includes a row for those days which have no records: AW> UkDate Total AW> 1/1/2003 5 AW> 2/1/2003 6 AW> 3/1/2003 0 AW> 4/1/2003 6 AW> I could post-process the record set to achieve this, but is there anyAW> way in 9i to do an aggregate query with an outer join on a date range AW> (if that makes sense)?
AW> Someone made the suggestion of creating another table with a row for AW> every day under the sun in it, against which you could inner join the AW> main query, but I'm not keen on that (that is just a gut response AW> though).
AW> Any ideas? Thanks!
AW> -- AW> Aidan Whitehall <mailto:aidanwhitehall_at_fairbanks.co.uk> AW> Macromedia ColdFusion Developer AW> Fairbanks Environmental Ltd +44 (0)1695 51775 AW> Queen's Awards Winner 2003 <http://www.fairbanks.co.uk/go/awards> AW> ________________________________________________________________________ AW> This e-mail has been scanned for all viruses by Star Internet. The AW> service is powered by MessageLabs. For more information on a proactive AW> anti-virus service working around the clock, around the globe, visit: AW> http://www.star.net.uk AW> ________________________________________________________________________AW> --
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: Jonathan Gennick
INET: jonathan_at_gennick.com
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: ListGuru_at_fatcity.com (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). Received on Wed Oct 29 2003 - 07:09:25 CST
 |
 |