| Full Outer Joins with more than 2 tables [message #294705] |
Fri, 18 January 2008 09:31  |
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  |
pablolee
Messages: 2882
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 |
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 #294736 is a reply to message #294705] |
Fri, 18 January 2008 13:55 |
 |
Kevin Meade
Messages: 2103
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
