Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2010
    Location
    Győr, Hungary
    Posts
    2

    Question Unanswered: "Column ambiguously defined" - Difference in Oracle 10 and 11

    Hi all,

    I am having a challenge when moving our application from Oracle 10g to 11g. I am (well, trying) to use Oracle 11.1.0.6.0 32-bit on a Windows 7.

    My problem drills down to the following query:
    Code:
    SELECT firstname, patient_status_cd, patient_address.city
      FROM patient
     INNER JOIN patient_address
        ON patient.patient_id = patient_address.patient_id
     INNER JOIN cd_patient_status
        ON patient.patient_status_cd = cd_patient_status.patient_status_cd;
    This query executes well on oracle 10, but on 11 an ORA-00918: column ambiguously defined is displayed instead for the patient_status_cd column.

    As I wrote, it runs well on 10g. But - strangely - if I change the query by changing the order of the JOIN parts, then it breaks on 10g as well. So this:
    Code:
    SELECT firstname, patient_status_cd, patient_address.city
      FROM patient
     INNER JOIN cd_patient_status
        ON patient.patient_status_cd = cd_patient_status.patient_status_cd;
     INNER JOIN patient_address
        ON patient.patient_id = patient_address.patient_id
    breaks on 10g as well.

    What I see is that Oracle has right - patient_status_cd is defined both in PATIENT and CD_PATIENT_STATUS tables. BUT, why does it run on 10g then???

    Thanks for your thoughts!

    Chris

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I don't know why, but I know that I *always* use table aliases and specify column names using those aliases. Nothing is ambiguous in that case.

  3. #3
    Join Date
    Mar 2010
    Location
    Győr, Hungary
    Posts
    2
    Yes, I have learned that now. However, my problem is that our application has hundreds of existing queries that are suspect to this issue - it would be a huge pain to re-test all of them...

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, that's the price of not following the best practices.

    However, you don't have to test them all - only those that won't work any more

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Littlefoot View Post
    Well, that's the price of not following the best practices.
    absolutely correct

    the original developers should be taken out and shot

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Jul 2003
    Posts
    2,296
    I've seen this before even in subqueries.
    No table alias is specified for columns that are compared and the developers wonder why they are getting strange results.

    there are many good reasons for table aliases and only one reason NOT to use aliases (laziness).

    one very good reason is simply for a 3rd party to easily read and understand the code.
    I frankly cannot stand someone sending me code to debug, and by looking at the code, I have no idea what columns belong to what tables.

    also consider the fact that you possibly break any and all code if/when you add additional columns to tables. add the same name? have a join? welcome to a broken application (or just incorrect results in your application).
    - The_Duck
    you can lead someone to something but they will never learn anything ...

Tags for this Thread

Posting Permissions

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