title: Column ambiguously defined in subquery using rownums
body: <p>I have to execute a SQL made from some users and show its results. An example SQL could be this:</p>
  
  <pre><code>SELECT t1.*, t2.* FROM table1 t1, table2 t2, where table1.id = table2.id
  </code></pre>
  
  <p>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.</p>
  
  <pre><code>SELECT z.* 
  FROM(       
      SELECT y.*, ROWNUM rn
      FROM (                   
          SELECT t1.*, t2.* FROM table1 t1, table2 t2, where table1.id = table2.id
      ) y      
      WHERE ROWNUM &lt;= 50) z         
  WHERE rn &gt; 0
  </code></pre>
  
  <p>This throws an exception: "ORA-00918: column ambiguously defined" because both Table1 and Table2 contains a field with the same name ("id").</p>
  
  <p>What could be the best way to avoid this?</p>
  
  <p>Regards.</p>
  
  <ul>
  <li>UPDATE</li>
  </ul>
  
  <p>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.</p>
  
  <p>I will mark Lex answer as it´s the solution we ended up working on.</p>