Column ambiguously defined in subquery using rownums

Question

I have to execute a SQL made from some users and show its results. An example SQL could be this:

SELECT t1.*, t2.* FROM table1 t1, table2 t2, where table1.id = table2.id

This SQL works fine as it is, but I need to manually add pagination and show the rownum, so the SQL ends up like this.

SELECT z.* 
FROM(       
    SELECT y.*, ROWNUM rn
    FROM (                   
        SELECT t1.*, t2.* FROM table1 t1, table2 t2, where table1.id = table2.id
    ) y      
    WHERE ROWNUM <= 50) z         
WHERE rn > 0

This throws an exception: "ORA-00918: column ambiguously defined" because both Table1 and Table2 contains a field with the same name ("id").

What could be the best way to avoid this?

Regards.

• UPDATE

In the end, we had to go for the ugly way and parse each SQL coming before executing them. Basically, we resolved asterisks to discover what fields we needed to add, and alias every field with an unique id. This introduced a performance penalty but our client understood it was the only option given the requirements.

I will mark Lex answer as it´s the solution we ended up working on.

Answer 1

I think you have to specify aliasses for (at least one of) table1.id and table2.id. And possibly for any other corresponding columnnames as well.

So instead of SELECT t1.*, t2.* FROM table1 t1, table2 use something like:

SELECT t1.id t1id, t2.id t2id [rest of columns] FROM table1 t1, table2 t2

I'm not familiar with Oracle syntax, but I think you'll get the idea.

Answer 2

I was searching for an answer to something similar. I was referencing an aliased sub-query that had a couple of NULL columns. I had to alias the NULL columns because I had more than one;

select a.*, t2.column, t2.column, t2.column (select t1.column, t1.column, NULL, NULL, t1.column from t1 where t1='VALUE') a left outer join t2 on t2.column=t1.column;

Once i aliased the NULL columns in the sub-query it worked fine.

Answer 3

If you could modify the query syntactically (or get the users to do so) to use explicit JOIN syntax with the USING clause, this would automatically fix the problem at hand:

SELECT t1.*, t2.*
FROM   table1 t1
JOIN   table2 t2 USING (id)

The USING clause does the same as ON t1.id = t2.id (or the implicit JOIN you have in the question), except that only one id column remains in the result, thereby eliminating your problem.

You would still run into problems if there are more columns with identical names that are not included in the USING clause. Aliases as described by @Lex are indispensable then.

Answer 4

Use replace null values function to fix this.

SELECT z.* 
FROM(       
    SELECT y.*, ROWNUM rn
    FROM (                   
        SELECT t1.*, t2.* FROM table1 t1, table2 t2, where 

        NVL(table1.id,0) = NVL(table2.id,0)

    ) y      
    WHERE ROWNUM <= 50) z         
WHERE rn > 0