<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.1//EN" "http://www.w3.org/TR/xhtml11/DTD/xhtml11.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head><style type="text/css"><!-- #msg DL { border : 1px #006 solid; background-color : #369; padding : 6px; color : #fff; } #msg DT { float : left; width : 6em; font-weight : bold; } #msg DL, #msg DT, #msg UL, #msg LI { font-family : arial,helvetica,sans-serif; font-size : 10pt; } h3 { font-family : arial,helvetica,sans-serif; font-size : 10pt; font-weight : bold; } #msg PRE { overflow : auto; white-space : normal; background-color : #ffc; border : 1px #fc0 solid; padding : 6px; } #msg UL, PRE, .diff { overflow : auto; } #patch h4 { font-family : arial,helvetica,sans-serif; font-size : 10pt; } #patch h4 { padding: 8px; background : #369; color : #fff; margin : 0; } #patch .propset h4, #patch .binary h4 {margin: 0;} #patch pre {padding:0;line-height:1.2em;margin:0;} #patch .diff {background:#eeeeee;padding: 0 0 10px 0;} #patch .propset .diff, #patch .binary .diff {padding: 10px 0;} #patch span {display:block;padding:0 10px;} #patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;} #patch .add {background:#ddffdd;} #patch .rem {background:#ffdddd;} #patch .lines, .info {color:#888888;background:#ffffff;} .diff { width : 100%; } #msg DL { border : 1px #006 solid; background-color : #369; padding : 6px; color : #fff; } #msg DT { float : left; width : 6em; font-weight : bold; } #msg DL, #msg DT, #msg UL, #msg LI { font-family : arial,helvetica,sans-serif; font-size : 10pt; } h3 { font-family : arial,helvetica,sans-serif; font-size : 10pt; font-weight : bold; } #msg PRE { overflow : auto; white-space : normal; background-color : #ffc; border : 1px #fc0 solid; padding : 6px; } #msg UL, PRE, .diff { overflow : auto; } #patch h4 { font-family : arial,helvetica,sans-serif; font-size : 10pt; } #patch h4 { padding: 8px; background : #369; color : #fff; margin : 0; } #patch .propset h4, #patch .binary h4 {margin: 0;} #patch pre {padding:0;line-height:1.2em;margin:0;} #patch .diff {background:#eeeeee;padding: 0 0 10px 0;} #patch .propset .diff, #patch .binary .diff {padding: 10px 0;} #patch span {display:block;padding:0 10px;} #patch .modfile, #patch .addfile, #patch .delfile, #patch .propset, #patch .binary, #patch .copfile {border:1px solid #ccc;margin:10px 0;} #patch .add {background:#ddffdd;} #patch .rem {background:#ffdddd;} #patch .lines, .info {color:#888888;background:#ffffff;} .diff { width : 100%; } --></style> <title>[rhq-project.org rhq] [2705] if multiple table aliases are used in the inner select list for a nested-pagination style query, oracle will throw "ORA-00918: column ambiguously defined'; </title> </head> <body> <div id="msg"> <dl> <dt>Revision</dt> <dd>2705</dd> <dt>Author</dt> <dd>jmarques</dd> <dt>Date</dt> <dd>2009-01-22 10:24:43 -0600 (Thu, 22 Jan 2009)</dd> </dl> <h3>Log Message</h3> <pre>if multiple table aliases are used in the inner select list for a nested-pagination style query, oracle will throw "ORA-00918: column ambiguously defined'; use direct manipulation of the WHERE clause to paginate the results, before the ordering is suffixed; </pre> <h3>Modified Paths</h3> <ul> <li><a href="#rhqtrunkmodulesenterpriseserverjarsrcmainjavaorgrhqenterpriseservereventEventManagerBeanjava">rhq/trunk/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/event/EventManagerBean.java</a></li> </ul> </div> <div id="patch"> <h3>Diff</h3> <a id="rhqtrunkmodulesenterpriseserverjarsrcmainjavaorgrhqenterpriseservereventEventManagerBeanjava"></a> <div class="modfile"><h4>Modified: rhq/trunk/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/event/EventManagerBean.java (2704 => 2705)</h4> <pre class="diff"> <span class="info">--- rhq/trunk/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/event/EventManagerBean.java 2009-01-22 13:44:54 UTC (rev 2704) +++ rhq/trunk/modules/enterprise/server/jar/src/main/java/org/rhq/enterprise/server/event/EventManagerBean.java 2009-01-22 16:24:43 UTC (rev 2705) </span><span class="lines">@@ -498,10 +498,12 @@ </span><span class="cx"> if (isFilled(source)) query += " AND upper(evs.location) LIKE ? "; if (!isCountQuery) { </span><span class="add">+ // add paging first as simple AND conditionals instead of using the nested query style + query = addPagingToQuery(query, pc); </span><span class="cx"> query = addSortingToQuery(query, pc); // NOTE: Add paging to the query last, since for Oracle, the whole query will become an inner SELECT in the // paging SELECTs. </span><span class="rem">- query = addPagingToQuery(query, pc); </span><span class="add">+ </span><span class="cx"> } return query; } </span><span class="lines">@@ -531,10 +533,14 @@ </span><span class="cx"> } else if (this.dbType instanceof OracleDatabaseType) { int minRowNum = pageControl.getStartRow() + 1; int maxRowNum = minRowNum + pageControl.getPageSize() - 1; </span><span class="rem">- queryWithPaging.append("SELECT * FROM (SELECT /*+ FIRST_ROWS(n) */ allResults.*, rownum rnum FROM ("); - queryWithPaging.append(query); - queryWithPaging.append(") allResults WHERE rownum <= ").append(maxRowNum).append(") WHERE rnum >= ") - .append(minRowNum); </span><span class="add">+ + queryWithPaging.append(" AND rownum <= ").append(maxRowNum); + queryWithPaging.append(" AND rownum >= ").append(minRowNum); + + // queryWithPaging.append("SELECT * FROM (SELECT /*+ FIRST_ROWS(n) */ allResults.*, rownum rnum FROM ("); + // queryWithPaging.append(query); + // queryWithPaging.append(") allResults WHERE rownum <= ").append(maxRowNum).append(") WHERE rnum >= ") + // .append(minRowNum); </span><span class="cx"> } else { throw new RuntimeException("Unknown database type : " + this.dbType); } </span> </pre> </div> </div> </body> </html> |