Solved

Getting rows for missing data from an Oracle table

Posted on 2009-07-10
4
465 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
  • 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
oracle query help 29 77
query returning everything 11 91
PLSQL procedure help with decode and null input params 5 46
Web Service from a stored procdure oracle 10 49
Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now