Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Posts
    15

    Unanswered: FULL JOIN on Views gives ERROR 00918, but LEFT(or RIGHT) JOIN doesn't

    When I run following query I get ORA-00918 (Column ambiguously defined).
    (FYI: PRO1MPAR and PAR_S are views)

    SELECT SUM(PRO1MPAR.qtyordered)
    FROM (select partnumber from PAR_S) PAR_S FULL JOIN
    (select partnumber, qtyordered from PRO1MPAR) PRO1MPAR on
    (PAR_S.partnumber = PRO1MPAR.partnumber )
    ;

    If I run the same query with 'LEFT(or RIGHT) JOIN' instead of 'FULL JOIN', I don't get the error.

    Does anyone know why would this happen?

    Thanks,
    Bhargav

  2. #2
    Join Date
    May 2004
    Location
    Dominican Republic
    Posts
    721
    I will theorize and say it is because of the following line:

    Code:
    ...
    (select partnumber, qtyordered from PRO1MPAR) PRO1MPAR on 
    ...
    You dont really need to select qtyordered there, since you are doing nothing with that column in the joinin procedence, thus it raises that error.

    Try rewriting your query like this:

    Code:
    SELECT SUM(PRO1MPAR.qtyordered) 
      FROM PAR_S FULL JOIN PRO1MPAR on PAR_S.partnumber = PRO1MPAR.partnumber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •