Solved

Getting rows for missing data from an Oracle table

Posted on 2009-07-10
4
466 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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Schema creation in Oracle12c 6 48
Oracle - SQL Query with Function 3 36
Oracle dataguard 5 32
DB migration from Mssql to 12c oracle , data not loading. 3 32
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
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

809 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