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  |
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
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed May 21 09:21:50 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
