Solved

Getting rows for missing data from an Oracle table

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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
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

730 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