both Subselects runs fine standalone.
But I wish to join them togehter, which always gives "ORA-00918: column ambiguously defined". I don't see why?
Both subelects uses other names.
SELECT *
FROM (SELECT aods.user_id,
aods.firstname,
aods.lastname,
Aods.DEPARTMENT,
cods.jc_name
FROM personas aods
LEFT JOIN
user_jc cods
ON aods.user_id = cods.user_id
WHERE cods.jc_name LIKE '%ADM%') ods
FULL OUTER JOIN
(SELECT abl.user_id,
abl.firstname,
abl.lastname,
Abl.DEPARTMENT,
bbl.ug_name
FROM personas abl
LEFT JOIN
ru_ug bbl
ON abl.user_id = bbl.user_id
WHERE Bbl.UG_NAME LIKE '%ADM%'
AND bbl.rss_name = 'TIR') bl
ON ods.user_id = bl.user_id
Because you do a select *
at the top.
user_id
firstname
lastname
DEPARTMENT
are present in both subqueries, and with select *
it would get two columns with the same name.
If you want to merge the two subqueries (such that the resulting set of columns would be user_id, firstname, lastname, DEPARTMENT, jc_name, ug_name
) you should do a natural join:
SELECT *
FROM (SELECT aods.user_id, aods.firstname, aods.lastname, aods.department,
cods.jc_name
FROM personas aods LEFT JOIN user_jc cods
ON aods.user_id = cods.user_id
WHERE cods.jc_name LIKE '%ADM%') ods
NATURAL JOIN
(SELECT abl.user_id, abl.firstname, abl.lastname, abl.department,
bbl.ug_name
FROM personas abl LEFT JOIN ru_ug bbl ON abl.user_id = bbl.user_id
WHERE bbl.ug_name LIKE '%ADM%' AND bbl.rss_name = 'TIR') bl
Instead if you want all the different columns, you should explicitly list them:
SELECT ods.user_id, ods.firstname, ods.lastname, ods.department, ods.jc_name,
bl.user_id, bl.firstname, bl.lastname, bl.department, bl.ug_name
FROM (SELECT aods.user_id, aods.firstname, aods.lastname, aods.department,
cods.jc_name
FROM personas aods LEFT JOIN user_jc cods
ON aods.user_id = cods.user_id
WHERE cods.jc_name LIKE '%ADM%') ods
FULL OUTER JOIN
(SELECT abl.user_id, abl.firstname, abl.lastname, abl.department,
bbl.ug_name
FROM personas abl LEFT JOIN ru_ug bbl ON abl.user_id = bbl.user_id
WHERE bbl.ug_name LIKE '%ADM%' AND bbl.rss_name = 'TIR') bl
ON ods.user_id = bl.user_id
In your comment you say that you want to use nvl()
on each column with an outer join. For me it works fine:
SELECT NVL (ods.user_id, bl.user_id), NVL (ods.firstname, bl.firstname),
NVL (ods.lastname, bl.lastname), NVL (ods.department, bl.department),
ods.jc_name, bl.ug_name
FROM (SELECT '1' user_id, 'ods2' firstname, NULL lastname,
'ods3' department, 'ods4' jc_name
FROM DUAL) ods
FULL OUTER JOIN
(SELECT '1' user_id, NULL firstname, 'bl2' lastname, 'bl3' department,
'bl4' ug_name
FROM DUAL) bl ON ods.user_id = bl.user_id
are you making some additional stuff, like order by
?