This content has been marked as final. Show 21 replies
Unfortunately, I cannot test out the join-syntax you are using at the moment! Having looked at the documentation though your syntax seems ok.
The errormessage is "ORA-00918: column ambiguously defined" marking a column inside the aggregate function.What is the exact aggregate-function syntax you have got in your query? And what column is being marked? You may need to re-look at your query and table structures to see why Oracle is not able to 'uniquely' identify that column? Is that column name present in both the tables involved in that particular inline view?
The query you have given doesn't seem to be complete. The example has has only one column specified in aggregate-function, that too qualified with the alias name. So, that column could not be giving you the error.
So, I think you need to provide the table structures and exact aggregate-function syntax you are using for us to be of any more help.
The syntax of join is correct and because USING clause is being used, the column used for join should not be qualified with a alias.
Here is a example from documentation:
SELECT department_id AS d_e_dept_id, e.last_nameAnd this is what is says about USING clause:
FROM departments d FULL OUTER JOIN employees e
ORDER BY department_id;
"...the USING clause coalesces the two matching columns department_id into a single column output:"
after a night full of sleep I condensed the original Query:
And I found, that the problem seems to be, that vsnt_vteile_lpfl is a view in a different schema. Because the query works correctly when I either copy the view into a table in my schema or join directly to the table in the other schema.
SELECT versnr FROM (SELECT versnr FROM tab1 GROUP BY versnr ) a FULL OUTER JOIN (SELECT vvl.vertragsnr versnr ,MAX(vvl.wivon) wivon FROM ev.vsnt_vteile_lpfl vvl GROUP BY vvl.vertragsnr ) b USING (versnr); ,MAX(vvl.wivon) wivon * FEHLER in Zeile 8: ORA-00918: column ambiguously defined
As before it also works when I omit the MAX(vvl.wivon).
A good one is also: if i write ",MAX(vvl.vertragsnr) wivon" the query works ;-)
Have you tried executing this inline view query independently and see whether it executes without error or not?
SELECT vvl.vertragsnr versnr
FROM ev.vsnt_vteile_lpfl vvl
GROUP BY vvl.vertragsnr;
Have you tried executing this inline view queryHi rhtakur,
independently and see whether it executes without
error or not?
yes, every part of the query individually executes, even the inline views.
Guys, I am running short of ideas here why this might be happening! Can anyone else try to have a look at this?
just for your information: its a bug, not a feature ;-)