Results 1 to 8 of 8
  1. #1
    Join Date
    Mar 2012

    Unanswered: Ambiguous joins in Oracle

    Dear all

    Can anyone tell me why ambiguous joins do not exist in Oracle when writing SQL? I've was trying to write some SQL in Access but was getting the error:
    Join is not supported.

    Eventually I had ago at writing it in the design view, rather than SQL view and then it gave me a more helpful reasons, it was an ambiguous join which I had defined.

    Not having come across such a thing in Oracle I thought I would look it up but I couldn't find anything explaining why you can't create an ambiguous join in Oracle but a reasonable number of explanations as to why you can in Access.

    King regards

  2. #2
    Join Date
    Nov 2003
    Provided Answers: 8
    I have no idea what you are talking about and as I can not see your screen nor the SQL you are writing I cannot even guess what you mean.

    If you had posted the SQL in question - well that would have been a terrific idea...

  3. #3
    Join Date
    Mar 2007
    It would be helpful if you defined what "ambiguous join" in Access means. Link to an article describing it would suffice; e.g. this one:

    Here, it looks like outer joining three tables via NULL values in the middle one. In my opinion, it is a design error.
    From the above threads it seems, that Oracle fails with ORA-01417 "a table may be outer joined to at most one other table" when using Oracle outer join operator, but converts the second outer join to inner one when using ANSI syntax. May be just Oracle version specific; it is hard to say without a test case - CREATE TABLE statements for table definitions (including primary and foreign key constraints), INSERT statements for some sample data.

  4. #4
    Join Date
    Mar 2012
    Sorry I should have explained what it meant. It is I believe the error is due to the way Jet cannot figure out which order to process multiple joins where one is an outer join and other is an inner join.

    In my case I had an inner join and then after the from statements a where clause.

    I didn't post the SQL because I don't appear to have the final query I typed last week and I'm struggling to recreate it exactly how I did.

    I could create it in design view but one is not allowed to switch to SQL view if the query is not correct, which isn't helpful because it's the SQL I wish to post.

    I think part of the problem is that I don't understand how to write JET SQL well enough so I keep making syntax errors and the error messages I get are not that helpful when I do. I also think the other problem is that I cannot do full joins in Access which would simplify my SQL.

    However I'm sure I could do the query I am wishing to run in Oracle. However I don't have Oracle access at the moment so can't test it out.
    Last edited by infobleep; 03-28-12 at 06:49.

  5. #5
    Join Date
    Mar 2012
    Dear all

    I now have an example of an ambiguous join in Access.
    SELECT PY2.*, 
    FROM py2 AS PY2, [Fe master] AS FEM1 
    LEFT JOIN [Feautre types to merge] AS FTTM 
      ON FTTM.NewFacilitiesTypeId=FEM1.FeatureTypeId 
    WHERE PY2.Feature_ID=FEM1.FeatureId AND
    That produces the error: JOIN expression not supported

    If I was working in the design view, it would tell me it was an ambiguous join. What I am curious to know is would it be an ambiguous join in Oracle? I can't test this out as I don't have Oracle access at the moment.

    Kind regards

  6. #6
    Join Date
    Aug 2003
    Where the Surf Meets the Turf @Del Mar, CA
    Provided Answers: 1
    >I can't test this out as I don't have Oracle access at the moment.
    I can not test post SQL because I don't have your tables.
    post CREATE TABLE statements for tables used by posted SQL,
    & I'll test for you.
    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.

  7. #7
    Join Date
    Mar 2007
    This query/error has nothing to do with the article I found (as it does not contain "ambiguous join" message anywhere).

    In this case, it seems that Access has special demands about placing parenthesis around joined tables:
    I wonder that you did not find the thread above (or any other one describing the same error).

    Additionally, it is very bad practice to combine "old" joins (,) with ANSI ones (INNER/OUTER/FULL JOIN) in one FROM clause. You should choose (and use) only one of them.

    I can't test this out as you did not provide any test case (as asked in my first post).

  8. #8
    Join Date
    Mar 2012

    ...and you can use Oracle for free for non-commercial use

    besides good advices from members, just to point out that you can download Oracle for free for non-commercial use (try the 11g Express Edition, its a light version very good for testing).

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