1 2 Previous Next 21 Replies Latest reply on Sep 6, 2006 5:46 AM by 216630 Go to original post
      • 15. Re: Problem with full outer join together with group functions
        514731
        Unfortunately, I cannot test out the join-syntax you are using at the moment! Having looked at the documentation though your syntax seems ok.

        You said:
        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.
        • 16. Re: Problem with full outer join together with group functions
          514731
          Hi Kumara,

          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_name
             FROM departments d FULL OUTER JOIN employees e
             USING (department_id)
             ORDER BY department_id;
          And this is what is says about USING clause:

          "...the USING clause coalesces the two matching columns department_id into a single column output:"
          • 17. Re: Problem with full outer join together with group functions
            216630
            OK,
            after a night full of sleep I condensed the original Query:
            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
            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.
            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 ;-)

            Regards
            Marcus
            • 18. Re: Problem with full outer join together with group functions
              514731
              Hi Marcus,

              Have you tried executing this inline view query independently and see whether it executes without error or not?
                      SELECT vvl.vertragsnr versnr
                             ,MAX(vvl.wivon)  wivon
                      FROM    ev.vsnt_vteile_lpfl vvl
                      GROUP BY vvl.vertragsnr;
              • 19. Re: Problem with full outer join together with group functions
                216630
                Have you tried executing this inline view query
                independently and see whether it executes without
                error or not?
                Hi rhtakur,
                yes, every part of the query individually executes, even the inline views.

                Regards
                Marcus
                • 20. Re: Problem with full outer join together with group functions
                  514731
                  Guys, I am running short of ideas here why this might be happening! Can anyone else try to have a look at this?
                  • 21. Re: Problem with full outer join together with group functions
                    216630
                    Hello,
                    just for your information: its a bug, not a feature ;-)

                    Marcus
                    1 2 Previous Next