"Column ambiguously defined" - Difference in Oracle 10 and 11

chris_k · #1 (**permalink**) 03-05-10, 03:25

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

Littlefoot · #2 (**permalink**) 03-05-10, 05:34

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.

chris_k · #3 (**permalink**) 03-05-10, 05:45

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...

Littlefoot · #4 (**permalink**) 03-05-10, 05:54

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

r937 · #5 (**permalink**) 03-05-10, 07:05

Quote:

Originally Posted by Littlefoot

Well, that's the price of not following the best practices.

absolutely correct

the original developers should be taken out and shot

The_Duck · #6 (**permalink**) 03-08-10, 15:33

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).

Internet Services

Web Development

Digital Marketing

Consumer Tech