Home » SQL & PL/SQL » SQL & PL/SQL » Full Outer Joins with more than 2 tables
Full Outer Joins with more than 2 tables [message #294705] Fri, 18 January 2008 09:31 Go to next message
chakravp
Messages: 2
Registered: January 2008
Junior Member
Hi,

Outer joins on more than 2 tables doesn't seem to work. Can anyone give any pointers to what mistake I might be making.

select A.x, B.y, C.z from A full outer join B on A.w = B.w full outer join C on A.w = C.w ---- does not work

select A.x, B.y, C.z from A join B on A.w(+) = B.w join C on A.w(+) = C.w ---- does not work

select A.x, B.y, C.z from A join B on A.w = B.w join C on A.w = C.w --- works

Thanks in advance,
chakravp
Re: Full Outer Joins with more than 2 tables [message #294709 is a reply to message #294705] Fri, 18 January 2008 09:45 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Full outer join on more than 2 tables Does work.
You say 'it doesn't work' prove it. show us a session:
open up sql plus, type in your code, run it, copy the session and paste it here between code tags making sure that the code does not extend beyond 80 characters per row otherwise it becomes difficult to read.
You could also post a test case (sample data. do NOT just post a list of data, post the create table and insert scripts for the tables with some representative data.)

[Updated on: Fri, 18 January 2008 09:46]

Report message to a moderator

Re: Full Outer Joins with more than 2 tables [message #294730 is a reply to message #294709] Fri, 18 January 2008 13:20 Go to previous messageGo to next message
chakravp
Messages: 2
Registered: January 2008
Junior Member
Here is the query in which full outer join does not work, but join works

with
tempTable as
(select substr("WRITETIME", 2, 6) as dt,
cast(substr("WRITETIME", 8, 2) as int) as hr,
("TOT_System_Up_Time"*100/3600) as avgUptime
from "NT_System_HV"
where "Server_Name" = 'myserver'
and "WRITETIME" between '0000000000000000' and '9999999999999999'
),

H1 as
(select dt,
avgUptime
from tempTable
where tempTable.hr = 0
),

H2 as
(select dt,
avgUptime
from tempTable
where tempTable.hr = 1
),

H3 as
(select dt,
avgUptime
from tempTable
where tempTable.hr = 2
)
select H1.dt as "DATE",
H1.avgUptime as "HOUR1",
H2.avgUptime as "HOUR2",
H3.avgUptime as "HOUR3"
from
H1 full outer join H2 on H1.dt = H2.dt
full outer join H3 on H1.dt = H3.dt

This results in the error:
ORA-00942: table or view does not exist
Error at line 4: column 3.

The same query works on replacing full outer join with join or inner join i.e., from the above query

H1 join H2 on H1.dt = H2.dt
join H3 on H1.dt = H3.dt --- WORKS

H1 full outer join H2 on H1.dt = H2.dt
join H3 on H1.dt = H3.dt --- WORKS

H1 full outer join H2 on H1.dt = H2.dt
full outer join H3 on H1.dt = H3.dt -- DOES NOT WORK





Re: Full Outer Joins with more than 2 tables [message #294732 is a reply to message #294730] Fri, 18 January 2008 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pablolee wrote on Fri, 18 January 2008 16:45
show us a session:
open up sql plus, type in your code, run it, copy the session and paste it here between code tags making sure that the code does not extend beyond 80 characters per row otherwise it becomes difficult to read.

Are you unable to do what is asked?
We are unable to help.

Regards
Michel

Re: Full Outer Joins with more than 2 tables [message #294736 is a reply to message #294705] Fri, 18 January 2008 13:55 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I would go Michel Pablolee one further and say, don't bother pasting sqlplus output, post for us a sqlplus script we can run ourselves to get our own sqlplus output.

Naturally this would require you to create a rebuildable test case that is simple to redo, test it yourself first to make sure it has no syntax errors, and infact reproduces the error, then paste in the code so we can run it ourselves.

Good luck, Kevin
Previous Topic: Change CLOB column to VARCHAR2 data type
Next Topic: Using bind variables with a variable length IN clause
Goto Forum:
  


Current Time: Tue Dec 06 11:56:27 CST 2016

Total time taken to generate the page: 0.25071 seconds