Simple SQL query getting “ORA-00918: column ambiguously defined”?

0
votes

I'm just trying to get the sum of money from a column.

SELECT SUM(amount_usd)
FROM WIRE_MSTR, TRANS_MSTR
INNER JOIN WIRE_MSTR ON WIRE_MSTR.trans_id = TRANS_MSTR.trans_id
WHERE WIRE_MSTR.dest_cntry = 'CANADA' AND TRANS_MSTR.trans_yyyymm = '201510';

But on line 4 I get an error "ORA-00918: column ambiguously defined." I've referenced everything, what could be the problem?

2
sqloracle
Can you include the SQL statements for defining your tables? - Simon Brahan
Oracle errors come from Oracle, not MySQL. - Gordon Linoff
When several tables are involved, qualify all columns! (E.g. SUM(tablename.amount_usd).) - jarlh

2 Answers

5
votes

You are using implicit and explicit join syntax. You should remove the implicit syntax:

SELECT SUM(amount_usd)
FROM TRANS_MSTR
INNER JOIN WIRE_MSTR ON WIRE_MSTR.trans_id = TRANS_MSTR.trans_id
WHERE WIRE_MSTR.dest_cntry = 'CANADA' AND TRANS_MSTR.trans_yyyymm = '201510';

The problem was, you had WIRE_MSTR twice in your FROM clause.

3
votes

Try this. You got WIRE_MSTR twice.

SELECT SUM(amount_usd)
FROM TRANS_MSTR
INNER JOIN WIRE_MSTR ON WIRE_MSTR.trans_id = TRANS_MSTR.trans_id
WHERE WIRE_MSTR.dest_cntry = 'CANADA' AND TRANS_MSTR.trans_yyyymm = '201510';