Simple Query

select * from c_master join e_master;

[No Problem]

BUT

select * from c_master c join e_master e;

[Could not execute statement. There is more than one way to join 'c' to 'e' SQLCODE=-147, ODBC 3 State="42000"]

Why is this?

asked 06 Dec '12, 05:22

Liam's gravatar image

Liam
36191118
accept rate: 0%

Can you please tell the version you are using, I can't reproduce your problem in 12.0.1.3769 so maybe it is an old bug?

(06 Dec '12, 07:10) Martin

Martin, I'm on 12.0.1.3750

I acknowledge Volker's comment below, but if the principle of name aliasing for the sake of readability introduces other complexities, it's not aliasing anymore is it?
There should be no reason why the plan execution cannot de-alias the statement to get it to the original form before building the relevant plans, is there?

(06 Dec '12, 07:57) Liam
Replies hidden

Just to clarify: Are there 2 FKs between these tables - and if so, how are they named?

(06 Dec '12, 08:02) Volker Barth

Volker, they are named according to a standard naming convention, i.e. fk_EMASTER_CMASTER, so there is no real correlation. I presume a NATURAL JOIN with same-named columns would not have the same problem. Will try it.

But my point still stands that aliasing should not compel you to rewrite your query.

(07 Dec '12, 06:53) Liam
Replies hidden

I agree on the "should not compel" point - however, I still have the impression there's something particular to your sample - feel free to show the FK relationships and their role name.

FWIW, as stated, I have never ever run into this issue:

  • Either there's only one FK relationship between the joined tables, and then aliasing doesn't lead to any disturbances,
  • or there is more than one FK relationships (or no at all), and then the join fails with SQLCODE -147:

-147 There is more than one way to join '%1' to '%2'

Probable cause
You are attempting to KEY JOIN two tables, and there are two or more foreign keys relating the two tables. There may be two foreign keys from the first table to the second table. It may be that each table has a foreign key to the other table. You must use a correlation name for the primary key table that is the same as the role name of the desired foreign key relationship.

As specified, using a fitting correlation name ensures even a working KEY JOIN when there are more than one FK between both tables (what I have never made use of personally).

So I still would think that in your sample, aliasing just leads to the fact that a previous "correlation name fits role name" situation is no longer true... - and here aliasing surely will make a difference, as docuemtned.

(07 Dec '12, 07:40) Volker Barth
1

Volker, you're right. There are in fact two foreign keys from table B to table A, both referring to the same PK in table A.

Thanks for the clarification.

(07 Dec '12, 08:52) Liam
showing 4 of 6 show all flat view

This doc page might give a clue:

As you are using JOIN without an explicit ON condition and without a further JOIN specifier, you are using a KEY JOIN. And this is somewhat dependent on the role name of the FK relationship, which seems to depend on the name of the table. I could imagine (but that's a guess) that using an ALIAS somewhat "disturbs" that search here - but that would only hold if there are more than one FKs between c_master and e_master, and one of them is named after the parent table - that would be the one used when not using an ALIAS).

FWIW, I regurlary use KEY JOINs with tables with ALIASed names and have never run into that issue. IMHO, KEY JOINs only fail (as expected) when there are more than 2 FK relationships (and in our cases, if so, they are both named differently than the parent table itself).

permanent link

answered 06 Dec '12, 05:59

Volker%20Barth's gravatar image

Volker Barth
30.8k308456665
accept rate: 32%

edited 06 Dec '12, 06:02

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×50

question asked: 06 Dec '12, 05:22

question was seen: 899 times

last updated: 07 Dec '12, 08:52