Home » SQL & PL/SQL » SQL & PL/SQL » SelfJoin?
SelfJoin? [message #376839] Thu, 18 December 2008 18:00 Go to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Hello,

Is there a thing like outer join that I can do with only one table?

I have one table that has dates for events. For instance, on what date was a product bought.

However these dates are not sequential. So I may have 10 items of product that were brought on december 16th and 20 on december 18th.

What kind of join can I use that tells me there were 0 bought on december 17th?

Thanks for your help!
@anacedent [message #376846 is a reply to message #376839] Thu, 18 December 2008 20:11 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Ok thanks does every oracle DB has a calendar table? Also what will be the primary key in this case?

Thank you
Re: SelfJoin? [message #376848 is a reply to message #376839] Thu, 18 December 2008 21:01 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
My query here is solved.

Thanks to Anacedent, who suggested that I create a Calendar table and then make an outer join on the table in question alongwith an NVL.

It is still conceptual--i am going to try and do it over the weekend. I will keep you posted what happened.

Thanks !!
Re: SelfJoin? [message #376850 is a reply to message #376848] Thu, 18 December 2008 21:16 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Hi;

I am back earlier than anticipated.

I searched for calendar on this forum--but could not find anything that may be helpful--or may be I saw something I could not see value in?

I also think that I do not have SQL Plus or PL/SQL. I have a third party sql query maker that I use to run the queries.

In this situation can i create a calendar that will solve my issue?

Thanks again

EA
Re: SelfJoin? [message #376880 is a reply to message #376850] Fri, 19 December 2008 00:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I searched for calendar on this forum--but could not find anything that may be helpful--or may be I saw something I could not see value in?

What does "could not find anything that may be helpful" mean? What should be helpful for you?

Quote:
I also think that I do not have SQL Plus or PL/SQL.

So you have not Oracle. PL/SQL is built in Oracle.

Quote:
I have a third party sql query maker that I use to run the queries.

And then? The solutions in the calendar topics you can find here does not depend on the query tool.

Regards
Michel
Re: SelfJoin? [message #376999 is a reply to message #376880] Fri, 19 December 2008 06:44 Go to previous messageGo to next message
mikeverkimpe
Messages: 30
Registered: April 2007
Location: Belgium
Member

Perhaps you need to create your own calendar table. I have a nice example, but it does require a year.

select to_date(lpad(rownum,3,'0')||'2008','DDDYYYY') from(
select 1,2,3,4,5,6,7,8,9 from dual group by cube (1,2,3,4,5,6,7,8,9))
where rownum <= to_char(to_date('31122008','DDMMYYYY'),'DDD')

Mike.
Re: SelfJoin? [message #377125 is a reply to message #376999] Sat, 20 December 2008 09:46 Go to previous messageGo to next message
EA123
Messages: 28
Registered: December 2008
Junior Member
Thank you. I solved this by creating a Calendar Table with the desired range of dates.

Then I wrote an outer join which included all dates and values.

Thanks for all your help!
Re: SelfJoin? [message #377128 is a reply to message #377125] Sat, 20 December 2008 10:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you need a calendar for just a query or a small set of queries or need calendar of different kinds you can also dynamically build it during the query itself:
SQL> with 
  2    cal as (
  3      select add_months(to_date('01/1980','MM/YYYY'), 3*(level-1)) quarter 
  4      from dual
  5      connect by level <= 12
  6    )
  7  select to_char(quarter,'YYYY"Q"Q') quarter, count(empno) hired
  8  from cal, emp
  9  where trunc(hiredate(+),'Q') = quarter
 10  group by quarter
 11  order by quarter
 12  /
QUARTE      HIRED
------ ----------
1980Q1          0
1980Q2          0
1980Q3          0
1980Q4          1
1981Q1          2
1981Q2          3
1981Q3          2
1981Q4          3
1982Q1          1
1982Q2          0
1982Q3          0
1982Q4          0

12 rows selected.

Regards
Michel
Re: SelfJoin? [message #377141 is a reply to message #377128] Sat, 20 December 2008 12:00 Go to previous message
EA123
Messages: 28
Registered: December 2008
Junior Member
Yes, Thank you.

I also thought about adding an extra column to the original table with the date datatype and the desired range of dates. But and a big but is that I do not want to mess the system tables as that might lead to undesired side effects??

Take care
EA
Previous Topic: Package Doubt
Next Topic: Dynamic Averages
Goto Forum:
  


Current Time: Wed Dec 07 09:03:03 CST 2016

Total time taken to generate the page: 0.12589 seconds