Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2004
    Posts
    6

    Exclamation Unanswered: Big issue -- "column ambiguous "

    Hi folks ,
    I am using following query in which AD_tab and AD_Table has primary key on AD_TABLE_ID . When I use following query , Oracle should column ambiguously error but its's not giving . Is it's a bug ?

    SQL > SELECT AD_Tab_ID FROM AD_Tab t INNER JOIN AD_Window w ON
    (t.AD_Window_ID=w.AD_Window_ID) INNER JOIN AD_Table tt ON
    (t.AD_Table_ID=tt.AD_Table_ID) WHERE AD_Table_ID=100 ORDER BY w.IsDefault DESC,
    t.SeqNo, ABS (tt.AD_Window_ID-t.AD_Window_ID) ;

    1 rows selected


    But when i fire following query it gives column ambigously defined error.


    SQL> SELECT AD_Tab_ID FROM AD_Tab t INNER JOIN AD_Table tt ON
    (t.AD_Table_ID=tt.AD_Table_ID) WHERE AD_Table_ID=100 ;
    ORA-00918: column ambiguously defined



    And suggestion ?



    --mksheoran

  2. #2
    Join Date
    Jan 2004
    Posts
    492
    My suggestion would be to not try to figure why its NOT giving you an error, but instead correct it to alias the column.
    So given the example:
    Code:
    select deptno
    from emp, dept
    where emp.deptno = dept.deptno
    This will throw an ambiguous column error because deptno is a column in both emp and dept tables. Instead alias is like so:

    Code:
    select emp.deptno
    from emp, dept
    where emp.deptno = dept.deptno
    In your code above, pick one of the tables that the column comes from, and use that as the alias. I think its always a good idea to alias columns especially when working with multiple tables. Dont make your future developers have to guess what table that column comes from.
    Oracle OCPI (Certified Practicing Idiot)

  3. #3
    Join Date
    Apr 2004
    Posts
    6

    Arrow IS IT's a ORACLE BUG !!

    Hi ,
    There are a lot of ways to run above mentioned query but I would like to know actual specification in regard of column ambiguity . First query is working fine but after a little bit modification (Secound query ) is giving column ambigous
    error ??????????


    Any Ideeaaaaa!!


    --mksheoran

  4. #4
    Join Date
    Apr 2004
    Posts
    6

    Actually it'a a bug

    It seem it's a bug of ORACLE.



    --mksheoran

  5. #5
    Join Date
    Nov 2004
    Location
    Temple University
    Posts
    36
    This is not a BUG, it is a feature.
    Everything should be in the form of
    schema_namd . table_alias . column_name

    SQL> SELECT AD_Tab_ID FROM AD_Tab t INNER JOIN AD_Table tt ON
    (t.AD_Table_ID=tt.AD_Table_ID) WHERE AD_Table_ID=100 ;
    ORA-00918: column ambiguously defined

    This should be
    select t.AD_Tab_id from ad_tab t, AD_Table tt where
    t.AD_talbe_id = tt.ad_table_id
    and
    t.ad_table_id = 100;

    Notice that EVERY column has a table alias as the prefix....
    This avoids the ambiguity problem....
    This query will only return the value of 100

    HTH
    Peter

  6. #6
    Join Date
    Oct 2004
    Posts
    145
    Oracle bug ? I be little more careful prior to making that type of assumption. If ANY database has bug in such a simple query they would not see the day light of database market.

    In any database, if you query a column that exists in two tables, you SHOULD prefix it with which table it belongs to.

  7. #7
    Join Date
    Jan 2004
    Posts
    492
    I admit I read this post 5 times before originally giving a followup to the first post. I STILL dont know what the original poster was going after. Simply prefix the damn column with a table/schema and you are all set. I think people just like to hear themselves talk (or type) sometimes.
    Oracle OCPI (Certified Practicing Idiot)

  8. #8
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    The original question posed is why doesn't he get an error, not why does he get an error.

    I have tried to format his original queries. Still doesn't make sense to me why he doesn't get the error. To my eyes the where clause AD_TABLE_ID should be ambiguous in both cases.

    Code:
    SELECT AD_Tab_ID
    FROM AD_Tab t 
        INNER JOIN AD_Window w ON
        (t.AD_Window_ID=w.AD_Window_ID) 
        INNER JOIN AD_Table tt ON
        (t.AD_Table_ID=tt.AD_Table_ID)
    WHERE AD_Table_ID=100
    ORDER BY w.IsDefault DESC,
                 t.SeqNo,
                 ABS (tt.AD_Window_ID-t.AD_Window_ID) ;
    
    1 rows selected
    Code:
    SELECT AD_Tab_ID
    FROM AD_Tab t
             INNER JOIN AD_Table tt ON
             (t.AD_Table_ID=tt.AD_Table_ID)
    WHERE AD_Table_ID=100 ;
    
    ORA-00918: column ambiguously defined
    NOTE: Please disregard the label "Senior Member".

  9. #9
    Join Date
    Oct 2004
    Posts
    145

    Oracle & ANSI Standard SQL

    Hum. Todd, thanks for the clarification. My mistake. First case should have thrown an error as well. The only thing I would ask that the poster verify is that if this type of error occurs in MS SQL Server or rules regarding the ANSI SQL.

    Although oracle does support ANSI standard SQL, I know for inner/outter joins oracle recommends use of =(+) rather than syntax INNER JOIN.

    Just found site that explains oracle and how it deals with ANSI standards.
    http://www.trivadis.ch/Images/nf9isq...tcm17-7272.pdf
    Last edited by JimYoo; 01-04-05 at 18:19. Reason: additional Info

Posting Permissions

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