Getting rows for missing data from an Oracle table

Hi,
I have a table in Oracle where there are rows for dates 24-Jun-09 and 29-Jun-09. I am making a query for a date range from 24-Jun to 30-Jun. Can I get the existing rows from the table plus 0 values rows for the dates missing from the table? Is that possible?
Thanks for your patience and support.
$wapnil
LVL 22
spattewarAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mrjoltcolaCommented:
Try this.

Change the starting date, plus the number of days (as indicated with the comment) to generate dynamically the date range you want.

Assumes tbl is your table and the column is date_col, and uses a left join so you'll get nulls for the rows with no entries, which you could override with NVL()
select * from (
select trunc(sysdate) - extract(day from to_date('24-JUN-09', 'dd-mon-yy')) + rownum day
from all_objects where rownum <= 14   -- change to the number of days you want the range to show
) d
left join (
select * from tbl
) t
on d.day = t.date_col
;

Open in new window

0
spattewarAuthor Commented:
the solution is perfect and is what I require. thank you very much!!!

One last question is that will this affect the performance or is there a better way to do it. for e.g. creating a gobal temp table and inserting all the rows, updating them and then selecting from it.

Thanks.
0
mrjoltcolaCommented:
Well this is just a clever trick to generate arbitrary dates on the fly. You could also do it completely without the all_objects table at all, I forget the technique, but it uses sys_connect_by syntax with dual table.

Actuall the all_objects query is a bit much as it is a complex view. You can choose any table with a given amount of rows to base it on, like a dummy table (see below).

The performance wont be bad, but you just simply document the query so someone else knows what it is doing as it will confuse any programmer who hasn't seen the technique.

Here is a more efficient version below using a dummy table pre-populated with 100 rows.
create table dummy(id integer primary key) organization index;
 
begin
  for i in 1..100 loop
     insert into dummy values(i);
  end loop;
end;
/
commit;
 
--Then redo the query to use DUMMY instead of ALL_OBJECTS
 
select * from (
select trunc(sysdate) - extract(day from to_date('24-JUN-09', 'dd-mon-yy')) + rownum day
from dummy where rownum <= 14   -- change to the number of days you want the range to show
) d
left join (
select * from tbl
) t
on d.day = t.date_col
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
spattewarAuthor Commented:
Excellent!
Thank for sharing your knowledge.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.