OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » SelfJoin?

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

SelfJoin? [message #376839]

Thu, 18 December 2008 18:00

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!

Report message to a moderator

@anacedent [message #376846 is a reply to message #376839]

Thu, 18 December 2008 20:11

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

Report message to a moderator

Re: SelfJoin? [message #376848 is a reply to message #376839]

Thu, 18 December 2008 21:01

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 !!

Report message to a moderator

Re: SelfJoin? [message #376850 is a reply to message #376848]

Thu, 18 December 2008 21:16

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

Report message to a moderator

Re: SelfJoin? [message #376880 is a reply to message #376850]

Fri, 19 December 2008 00:47

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SelfJoin? [message #376999 is a reply to message #376880]

Fri, 19 December 2008 06:44

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.

Report message to a moderator

Re: SelfJoin? [message #377125 is a reply to message #376999]

Sat, 20 December 2008 09:46

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!

Report message to a moderator

Re: SelfJoin? [message #377128 is a reply to message #377125]

Sat, 20 December 2008 10:09

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: SelfJoin? [message #377141 is a reply to message #377128]

Sat, 20 December 2008 12:00

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

Report message to a moderator

Previous Topic:	Package Doubt
Next Topic:	Dynamic Averages

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Dec 06 16:40:06 CST 2024