Re: HELP... Oracle SQL Date question...
Date: 1999/01/10
Message-ID: <915976507.14312.0.nnrp-12.9e984b29_at_news.demon.co.uk>#1/1
A fairly common trick for filling in blank columns like this is to create a table a single number column, filling it with the integers from 0 to N (where N is as large as you want).
You can then execute your query as an in-line query, and make the result the target of an outer join from your number table.
e.g.
select
order_date, nvl(order_count)
from
number_driver num,
(
SELECT
to_char(order_date,'DD-MON-YY') order_date,
countt(order_id) order_count
FROM orders
GROUP BY to_char(order_date,'DD-MON-YY') ) ord
where
ord.order_date(+) = to_date('1-jan-1999','dd-mon-yyyy') + num.num_column
order by num.num_column
;
There are a few details to fill in, such as how you avoid dumping a load of wasteful zeros before and after the meat of the report, but it is a 'pure SQL' option to consider
Jonathan Lewis
Yet another Oracle-related web site: www.jlcomp.demon.co.uk
keith kwiatek wrote in message <77a8f5$g10$1_at_news.nist.gov>...
>Hello Smart One!
>
>I have an oracle table called "orders", that records the date a user
>completed an on-line purchase...
>
>I can write a query that will do a "group by" to show all the dates and the
>total number of orders for each date...that is simple:
>
>SELECT to_char(order_date,'DD-MON-YY'), count(order_id)
>FROM orders
>GROUP BY to_char(order_date,'DD-MON-YY')
>
>and it will generate something like:
>
>1-JAN-99 34
>2-JAN-99 55
>4-JAN-99 41
>
>
>BUT, notice in the above example that on 3-JAN-99 there were no orders! And
>indeed, there are no records in the ORDERS table for that date...SO, how do
>I write a query that will print out '03-JAN-99 0' , instead of just
>skippng the date altogether?
>
>In other words, when there are no records for a given date (03-JAN-99), I
>want the SQL to print 0 (zero) instead of skipping the date (it must do
this
>across months and years):
>
>1-JAN-99 34
>2-JAN-99 55
>3-JAN-99 0
>4-JAN-99 41
>
>
>
>|Thanks!
>Please email me at
>
>Regards,
>Keith
>
>
Received on Sun Jan 10 1999 - 00:00:00 CET