Results 1 to 12 of 12
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Unanswered: Ambiguity in SELECT statements with views

    Running Oracle Database 11g Release 11.1.0.7.0 - 64bit Production

    I am currently porting our software to run with ORACLE and have come across the following problem.

    VIEW SQL :-
    CREATE VIEW supplieraccountinfo AS
    SELECT T.id AS supplierid, T.balance, T.onorderval AS onordervalue
    FROM traders T
    WHERE T.tradertype LIKE 'S%'

    SELECT SQL:-
    SELECT id, name, source
    FROM traders
    INNER JOIN supplieraccountinfo SI ON traders.id = SI.supplierid

    Running the select you get:
    "ORA-00918: column ambiguously defined"

    Now this SQL is valid on SQL SERVER, DB2 & INFORMIX but not ORACLE.

    Now as far as I'm aware, through all my years of using SQL you should not need to qualify "id" as it is unique to the traders table. The supplieraccountinfo view has supplierid and onordervalue columns only.

    It seems to be that if a column is used in the JOIN criteria and it is also in the select list then it must be either unique at the base (table) level across all of the referenced tables/views or else qualified.

    Proof of this is that if you change the join and replace "traders.id = SI.supplierid" with "traders.balance = SI.balance" then the SQL is OK.

    Any ORACLE SQL experts comments on this are most welcome.

    regards

    Andy

  2. #2
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Quote Originally Posted by andrewhallam
    Now as far as I'm aware, through all my years of using SQL you should not need to qualify "id" as it is unique to the traders table. The supplieraccountinfo view has supplierid and onordervalue columns only.
    Sorry - that should read:

    Now as far as I'm aware, through all my years of using SQL you should not need to qualify "id" as it is unique to the traders table. The supplieraccountinfo view has supplierid, BALANCE and onordervalue columns only.

    Andy

  3. #3
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    when you type

    desc supplieraccountinfo


    what do you see?


    try running the following command.


    Code:
    CREATE OR REPLACE VIEW supplieraccountinfo AS 
    SELECT T.id  supplierid, T.balance, T.onorderval  onordervalue
    FROM traders T 
    WHERE T.tradertype LIKE 'S%';
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  4. #4
    Join Date
    Jun 2009
    Posts
    7
    views are nothing but query stored in db , whenever view is used basically that query is called. Hence in this case we accessing traders object twice and hence the error ambiguously defined.

    Here is explain plan of above query when alias is used for id column

    ------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 46 | 5 (20)| 00:00:01 |
    |* 1 | HASH JOIN | | 1 | 46 | 5 (20)| 00:00:01 |
    | 2 | TABLE ACCESS FULL| TRADERS | 1 | 26 | 2 (0)| 00:00:01 |
    |* 3 | TABLE ACCESS FULL| TRADERS | 1 | 20 | 2 (0)| 00:00:01 |
    ------------------------------------------------------------------------------

  5. #5
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Quote Originally Posted by beilstwh

    desc supplieraccountinfo
    gets:

    Name Null Type
    ------------------------------ -------- -------------
    SUPPLIERID NOT NULL NVARCHAR2(30)
    BALANCE NOT NULL NUMBER(19,4)
    ONORDERVALUE NUMBER(19,4)

  6. #6
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Quote Originally Posted by sardb
    views are nothing but query stored in db , whenever view is used basically that query is called. Hence in this case we accessing traders object twice and hence the error ambiguously defined.
    Yes I know that you can think of a view as 'just' a 'compiled' SELECT statement but as with all other DB providers that I have come across they are a little more sophisticated than you imply.

    You should (and can with all the other main DB vendors) be able to reference a view as a totally independent object and not think of it with regard to what it is based on.

    We have a very large product with hundreds of very complex SQL statements and views. Some of these are based on multiple self joins etc and views are the industry standard to simplifying SQL statements.

    I will do some more investigation for I cannot believe that a vendor such as ORACLE can be so different with regard to view SQL.

    Andy

  7. #7
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    does error still occur for SQL below?

    Code:
    SELECT id, name, source
    FROM traders
    WHERE traders.id in (SELECT SI.supplierid  FROM supplieraccountinfo SI )
    /
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  8. #8
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Code:
    SELECT id, name, source
    FROM traders
    WHERE traders.id in (SELECT SI.supplierid  FROM supplieraccountinfo SI )
    /
    Well yes this works but it doesn't help.
    The example I have given is a very much simplified version of one of our actual statements.
    It needs to use FULL ANSI SQL syntax.

  9. #9
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by andrewhallam
    Now as far as I'm aware, through all my years of using SQL you should not need to qualify "id" as it is unique to the traders table. The supplieraccountinfo view has supplierid, BALANCE and onordervalue columns only.
    It won't help you, but I can confirm that this is not working as expected on 11g installation (tried it with 11.1.0.6.0 on Windows XP)
    I too would have expected the column not to be ambigous.

    I tried this with 10.2.0.3.0 and there it is working as expected i.e. there is no error.

    Seems to be a regression in 11.x
    Extremely annoying I agree.

    Did you search metalink? Maybe there is a patch for that already although I doubt it.

  10. #10
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Yes shammat - I concur with your findings.

    Running the same test on 10g (10.2.0.3.0) the error does not occur.

    My simplified test scenario is thus:

    CREATE TABLE mytable (mycol NVARCHAR2(10), mycol2 NVARCHAR2(10));

    CREATE VIEW myview AS SELECT mycol AS v_mycol, mycol2 AS v_mycol2 FROM mytable;

    SELECT mycol
    FROM mytable
    INNER JOIN myview ON mytable.mycol = myview.v_mycol;

    - Above works on 10g (10.2.0.3.0) but fails with “ORA-00918: column ambiguously defined” on 11g (11.1.0.7.0)

    SELECT mycol
    FROM mytable
    LEFT OUTER JOIN myview ON mytable.mycol = myview.v_mycol;

    - Above works on both 10g and 11g.

    I have raised a log through Metalink.

    Will post a reply when I hear anything.

    Andy

  11. #11
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    Confirmed with Oracle through Metalink support log that this is indeed a bug.

    Oracle have also confirmed this as a bug in:
    10.2.0.4
    11.1.0.6
    11.1.0.7 (where I found it)

    The fix for this is not yet included in 11.1.0.7 patch bundles.

    Workaround:
    Do not use ANSI JOINS.
    Prefix the column name.
    Use 10.2.0.3.

    This Oracle port is going to be fun...

    Andy

  12. #12
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52
    For info.

    Now fixed in 11.1.0.7 patch set 14.

    Regards,
    Andy

Posting Permissions

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