?
Solved

Getting rows for missing data from an Oracle table

Posted on 2009-07-10
4
Medium Priority
?
470 Views
Last Modified: 2013-12-19
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
0
Comment
Question by:spattewar
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
4 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24824791
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
 
LVL 22

Author Comment

by:spattewar
ID: 24825479
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
 
LVL 40

Accepted Solution

by:
mrjoltcola earned 2000 total points
ID: 24825719
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
 
LVL 22

Author Closing Comment

by:spattewar
ID: 31602131
Excellent!
Thank for sharing your knowledge.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Via a live example, show how to take different types of Oracle backups using RMAN.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question