-
problem with left outer join
I have a sybase query that must be converted to an oracle query but Im getting the error
'a table may be outer joined to at most one other table'
the query used to work in Sybase (i didnt originally write it, I just have to convert it) heres the SQL
Select x, y, z (just for example purpose)
FROM tmp_epg_txs_ids etd,
txs_containers tc,
aspect_ratios ar,
formats f,
ancsvc st,
epg_templates et
WHERE etd.id = tc.id
AND tc.aspect_ratio_id (+)= ar.id
AND tc.format_id (+)= f.id
AND tc.ansv_method_st_code (+)= st.ancsvc_cod
AND tc.epg_template_id (+)= et.id
can anyone help ?
TIA
-
Hi.
For a left outer join in Oracle, the (+) goes on the right hand side of the "=" to indicate that this item might not be present. Assuming that "etd" and "tc" are present and the other rows may not be you should writ either of these statements in Oracle:
Select x, y, z
FROM tmp_epg_txs_ids etd,
txs_containers tc,
aspect_ratios ar,
formats f,
ancsvc st,
epg_templates et
WHERE etd.id = tc.id
AND tc.aspect_ratio_id = ar.id (+)
AND tc.format_id = f.id (+)
AND tc.ansv_method_st_code = st.ancsvc_cod (+)
AND tc.epg_template_id = et.id (+);
Select x, y, z
FROM tmp_epg_txs_ids etd
JOIN txs_containers tc ON etd.id = tc.id
LEFT OUTER JOIN aspect_ratios ar ON tc.aspect_ratio_id = ar.i
LEFT OUTER JOIN formats f ON tc.format_id = f.id
LEFT OUTER JOIN ancsvc st ON tc.ansv_method_st_code = st.ancsvc_cod
LEFT OUTER JOIN epg_templates et ON tc.epg_template_id = et.id;
Cheers
Tim...
-
left outer join
thanks Tim
Im a newbie to oracle, having come from SQL server where left outer join was *= and right outer join was =* (its the same in sybase also), its a bit confusing !
Your solution worked, thank you.
-
At the risk of confusing misuk11 further, I prefer to write joins the other way round, in the way that my brain considers "forwards". My thinking here (and I appreciate other people seem to be wired up differently) is that you would not generally write
Code:
AND 'Smith' = e.ename
and SQL syntax does not allow you to write
Code:
AND NULL IS e.deptno
You instead put the the column to test on the left, and the value or expression you have already established on the right. Thus,
Code:
AND e.ename = 'Smith'
AND e.deptno IS NOT NULL
and likewise
Code:
SELECT x, y, z
FROM tmp_epg_txs_ids etd
, txs_containers tc
, aspect_ratios ar
, formats f
, ancsvc st
, epg_templates et
WHERE tc.id = etd.id
AND ar.id (+) = tc.aspect_ratio_id
AND f.id (+) = tc.format_id
AND st.ancsvc_cod (+) = tc.ansv_method_st_code
AND et.id (+) = tc.epg_template_id;
SELECT x, y, z
FROM tmp_epg_txs_ids etd
JOIN txs_containers tc ON tc.id = etd.id
LEFT OUTER JOIN aspect_ratios ar ON ar.i = tc.aspect_ratio_id
LEFT OUTER JOIN formats f ON f.id = tc.format_id
LEFT OUTER JOIN ancsvc st ON st.ancsvc_cod = tc.ansv_method_st_code
LEFT OUTER JOIN epg_templates et ON et.id = tc.epg_template_id;
Whichever way around you write it, all SQL cares about is that the (+) operator is immediately to the right of the key from the outer joined table.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|