column ambiguously defined in oracle

Question

I am no sql expert. I was trying to run following query on my oracle sqlplus prompt

select 0 AS initVal, 
loadTable.amount from 
accountsTable JOIN loadTable ON num=accNum ,
loadTable JOIN loanTable ON loadTable.numSeq=loanTable.numSeq

and getting following error

column ambiguously defined, which is pointing to loadTable.amount in select clause.

Little bit I am able to understand that there is a cartesian product of accountsTable JOIN loadTable ON num=accNum and loadTable JOIN loanTable ON loadTable.numSeq=loanTable.numSeq after which it is not able to understand from which table to pick loadTable.amount, since result of both has this column. Is there any way I can get rid of this problem without specifying another join between these two intermediate tables on loadTable.amount ?

Gordon Linoff · Accepted Answer · 2014-06-08T18:52:13.690

2

This is your query:

select 0 AS initVal, loadTable.amount
from accountsTable JOIN
     loadTable
     ON num=accNum ,
     loadTable JOIN
     loanTable
     ON loadTable.numSeq=loanTable.numSeq;

Your query has loadtable twice, which I don't think you intend. And, the last condition is a tautology, because the value is coming from the same table. Also, you should use table aliases that are abbreviations -- it makes queries easier to write and read. I think this is closer to what you are trying to do:

select 0 AS initVal, lt.amount
from accountsTable act JOIN
     loadTable lt
     ON act.num = lt.accNum JOIN
     loanTable lot
     ON lt.numSeq = lot.numSeq;

As a general rule: Never use comma in from statements. Always connect the tables by the appropriate join.

EDIT:

If you think your query is "logically" correct, you should put in table aliases and be specific about the joins:

select 0 AS initVal, lot1.amount
from accountsTable act JOIN
     loadTable lot1
     ON act.num = lot1.accNum CROSS JOIN
     loadTable lot2 JOIN
     loanTable lnt
     ON lot1.numSeq = lot2.numSeq;

Those join conditions look non-sensical to me, especially because the query pulls the amount from only one table, so the second table doesn't even seem to be used.

edited Jun 08 '14 at 18:52

answered Jun 08 '14 at 18:37

Gordon Linoff

1,242,037
58
646
786

hey wait a min.. you converted the query from 3 joins to 2 joins. I did intend 3 join (2 with condition and one which is done by using comma without join condition aka cartesian product) – peeyush Jun 08 '14 at 18:44
You should edit your question and show your sample data and desired results. – Gordon Linoff Jun 08 '14 at 18:49
so by any chance you mean both are going to give same result ? I am sensing that. – peeyush Jun 08 '14 at 18:51
Thanks a lot. I can understand that currently my logically correct might be looking non-sensible. but this is the case. Thanks for suggesting aliases which can solve this problem. – peeyush Jun 08 '14 at 19:19
It would help if you would just describe as best you can exactly what information you want the query to provide. We can't judge correctness unless we know what you are trying to do. – TommCatt Jun 10 '14 at 21:28

column ambiguously defined in oracle

1 Answers1