0

I am new to Oracle and working with a fairly large database. I would like to perform a query that will select the desired columns, order by a certain column and also limit the results. According to everything I have read, the below query should be working but it is returning "ORA-00918: column ambiguously defined":

SELECT * FROM(SELECT * FROM EAI.EAI_EVENT_LOG e, 
     EAI.EAI_EVENT_LOG_MESSAGE e1 WHERE e.SOURCE_URL LIKE '%.XML' 
     ORDER BY e.REQUEST_DATE_TIME DESC) WHERE ROWNUM <= 20

Any suggestions would be greatly appreciated :D

Dave Lasley
  • 5,072
  • 1
  • 32
  • 37

3 Answers3

6

The error message means your result set contains two columns with the same name. Each column in a query's projection needs to have a unique name. Presumably you have a column (or columns) with the same name in both EAI_EVENT_LOG and EAI_EVENT_LOG_MESSAGE.

You also want to join on that column. At the moment you are generating a cross join between the two tables. In other words, if you have a hundred records in EAI_EVENT_LOG and two hundred records EAI_EVENT_LOG_MESSAGE your result set will be twenty thousand records (without the rownum). This is probably your intention.

"By switching to innerjoin, will that eliminate the error with the current code?"

No, you'll still need to handle having two columns with the same name. Basically this comes from using SELECT * on two multiple tables. SELECT * is bad practice. It's convenient but it is always better to specify the exact columns you want in the query's projection. That way you can include (say) e.TRANSACTION_ID and exclude e1.TRANSACTION_ID, and avoid the ORA-00918 exception.

APC
  • 137,061
  • 19
  • 153
  • 266
  • Ok I think I'm understanding. You are correct in presuming both tables have a like column (TRANSACTION_ID)....from your explanation, I am being extremely inefficient by essentially creating a dataset of tableACols^tableBCols, I will switch up to an innerjoin for sure. By switching to innerjoin, will that eliminate the error with the current code? /wishIwasStillAtWorkToTry :P – Dave Lasley Aug 29 '11 at 14:55
2

Maybe you have some columns in both EAI_EVENT_LOG and EAI_EVENT_LOG_MESSAGE tables having identical names? Instead of SELECT * list all columns you want to select.

Other problem I see is that you are selecting from two tables but you're not joining them in the WHERE clause hence the result set will be the cross product of those two table.

bpgergo
  • 14,789
  • 2
  • 37
  • 60
1

You need to stop using SQL '89 implicit join syntax.
Not because it doesn't work, but because it is evil.
Right now you have a cross join which in 99,9% of the cases is not what you want. Also every sub-select needs to have it's own alias.

SELECT * FROM
(SELECT e.*, e1.* FROM EAI.EAI_EVENT_LOG e
 INNER JOIN EAI.EAI_EVENT_LOG_MESSAGE e1 on (......)
 WHERE e.SOURCE_URL LIKE '%.XML' 
 ORDER BY e.REQUEST_DATE_TIME DESC) s WHERE ROWNUM <= 20

Please specify a join criterion on the dotted line.

Normally you do a join on a keyfield e.g. ON (e.id = e1.event_id)

It's bad idea to use select *, it's better to specify exactly which fields you want:

SELECT e.field1 as customer_id
       ,e.field2 as customer_name
.....
Johan
  • 71,222
  • 23
  • 174
  • 298
  • ROWNUM is applied to the result set, not the tables. – APC Aug 29 '11 at 14:40
  • Awesome, this actually looks a lot closer to the mySQL I know and love. Just one question, where does the s come from in s WHERE s.ROWNUM <= 20...or is the s being defined, and then immediately referenced? – Dave Lasley Aug 29 '11 at 14:46
  • @Dave, my bad, ROWNUM is not a real column, it's a pseudocolumn. The s is defined and then immediately referenced. Only on a real column of cause, not on a pseudo column. – Johan Aug 29 '11 at 14:50