CODEWITHSUNDEEP
oracle-databaseoracle11g
Dave Lasley
Dave Lasley

Reputation: 6242

Oracle Select Query, Order By + Limit Results

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

Upvotes: 0

Views: 2809

Answers (3)

APC
APC

Reputation: 146219

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.

Upvotes: 6

Johan
Johan

Reputation: 76537

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
.....

Upvotes: 1

bpgergo
bpgergo

Reputation: 16037

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.

Upvotes: 2

Related Questions