for a particular table, the data may look like this
All the way to Aug30
Whenever there is no data, there will be no entry in the table (eg. Aug3)
Now what I want to do is join the above 4 tables
How would I write my query so that if one of the tables has data for a particular day, this would be shown in the result, while the tables without data for that day would show 0.
I believe up to Oracle 9, ANSI join syntax was not supported, and from 9 onwards was bugged in some versions... this gives you (old but workiing Oracle) syntax....
select t1.datecol, t2.datecol, t3,datecol
where t2.datecol (+) = t1.datecol and
t3.datecol (+) = t2.datecol and
this will of course only return dates that already exist in T1, ie if it doesnt exist there you won't get any row at all.
I suspect however that you want all dates for a date range, in which case the SQL will be somewhat more complex - probably resorting to our good freind user_objects with a typecast of rownum to the first date in your range.
yes, sorry. Sometimes you want to generate lots of values purely to seed a query such as yours with data. USER_OBJECTS is a view on Oracle which (usually) contains lots of rows. ALL_OBJECTS will definately contain enough for you to satisfy a month of dates.
USER_OBJECTS contains a row for every object owned by your schema(user), and ALL_OBJECTS - yes, one for every object on the database. An object being a table/view/trigger/package etc.
You might for examples do this...
where rownum <=31
This gives a resultset guaranteed to contain enough dates for each date of any month following 31st August. You would then use this as the base table in your joins (usually as a subselect - or inline query).
from (inline query above but using some date ranges) b,
where t1.datecol (+) = b.datecol and
t2.datecol (+) = t1.datecol ..... etc for all tables.
Oracle has some methods for obtaining first day of month, last day of month etc. This still may not do exactly what you want, if not, post exactly what you want and someone (or I) will attempt to help.
insert into test1 values (to_date('01/09/2003','DD/MM/YYYY'),1);
insert into test1 values (to_date('02/09/2003','DD/MM/YYYY'),2);
insert into test2 values (to_date('02/09/2003','DD/MM/YYYY'),1);
insert into test2 values (to_date('03/09/2003','DD/MM/YYYY'),2);
insert into test3 values (to_date('03/09/2003','DD/MM/YYYY'),1);
insert into test3 values (to_date('04/09/2003','DD/MM/YYYY'),2);
insert into test4 values (to_date('04/09/2003','DD/MM/YYYY'),1);
insert into test4 values (to_date('05/09/2003','DD/MM/YYYY'),2);
select datecol, sum( t1 ), sum( t2 ), sum( t3 ), sum( t4 )
select datecol, totval as t1, 0 as t2, 0 as t3, 0 as t4 from test1
select datecol, 0 as t1, totval as t2, 0 as t3, 0 as t4 from test2
select datecol, 0 as t1, 0 as t2, totval as t3, 0 as t4 from test3
select datecol, 0 as t1, 0 as t2, 0 as t3, totval as t4 from test3
group by datecol