Problem with Class DB2Platform function printSQLSelectStatement [message #1027847] |
Wed, 27 March 2013 13:46 |
Xanas Aurel Messages: 2 Registered: March 2013 |
Junior Member |
|
|
The function printSQLSelectStatement in Class DB2Platform generates following Query if I use MaxResults.
SELECT * FROM (SELECT * FROM (SELECT EL_TEMP.*, ROWNUMBER() OVER() AS EL_ROWNM FROM (SELECT t1.ID AS a1, ...
FROM AUFTRAG_KOPF t1
LEFT OUTER JOIN SYSTEM_KEYS t0 ON (t0.ID = t1.ARBEITSGRUPPE_ID)
LEFT OUTER JOIN MITARBEITER t2 ON (t2.ID = t1.BERICHT_MITARB_ID)
LEFT OUTER JOIN SYSTEM_KEYS t3 ON (t3.ID = t1.AUFTRAG_TYP_ID)
ORDER BY t0.NAME ASC)
AS EL_TEMP)
AS EL_TEMP2
WHERE EL_ROWNM <= 50) AS EL_TEMP3 WHERE EL_ROWNM > 0
But I didn't get the expected result, because the ORDER BY must between the OVER brackets.
The Query have to look like this.
SELECT * FROM (SELECT ROWNUMBER() OVER(ORDER BY t0.NAME) EL_ROWNM, t1.ID, ...
FROM AUFTRAG_KOPF t1
LEFT OUTER JOIN MITARBEITER t0 ON t0.ID = t1.BERICHT_MITARB_ID
LEFT OUTER JOIN SYSTEM_KEYS t2 ON t2.ID = t1.AUFTRAG_TYP_ID
LEFT OUTER JOIN SYSTEM_KEYS t3 ON t3.ID = t1.ARBEITSGRUPPE_ID) AS EL_TEMP
WHERE EL_ROWNM BETWEEN 0 AND 50
Now I get the right result.
Is it a Bug or I'm doing wrong?
Xanas
[Updated on: Mon, 08 April 2013 11:15] Report message to a moderator
|
|
|
|
Re: Problem with Class DB2Platform function printSQLSelectStatement [message #1036496 is a reply to message #1028747] |
Mon, 08 April 2013 13:01 |
Xanas Aurel Messages: 2 Registered: March 2013 |
Junior Member |
|
|
For now I fixed the problem with the following code, not very nice but it works for now.
If I want to make it right, I have to rewite the complete statement.printSQL funtion.
@Override
public void printSQLSelectStatement(DatabaseCall call, ExpressionSQLPrinter printer, SQLSelectStatement statement) {
int max = 0;
int firstRow = 0;
if (statement.getQuery() != null) {
max = statement.getQuery().getMaxRows();
firstRow = statement.getQuery().getFirstResult();
}
if (!(this.shouldUseRownumFiltering()) || (!(max > 0) && !(firstRow > 0))) {
super.printSQLSelectStatement(call, printer, statement);
return;
} else if (max > 0) {
statement.setUseUniqueFieldAliases(true);
String overRequest = generateOverRequest(statement);
call.setFields(statement.printSQL(printer));
String sql = printer.getWriter().toString();
CharArrayWriter w = (CharArrayWriter) printer.getWriter();
w.reset();
printer.printString("SELECT * FROM (");
printer.printString(sql.replaceFirst(" FROM ", ", ROWNUMBER() OVER (" + overRequest + ") AS EL_ROWNM FROM ")); // FROM
printer.printString(") AS EL_TEMP WHERE EL_ROWNM > ");
printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD);
printer.printString(" AND EL_ROWNM <=");
printer.printParameter(DatabaseCall.MAXROW_FIELD);
} else {// firstRow>0
statement.setUseUniqueFieldAliases(true);
String overRequest = generateOverRequest(statement);
call.setFields(statement.printSQL(printer));
String sql = printer.getWriter().toString();
CharArrayWriter w = (CharArrayWriter) printer.getWriter();
w.reset();
printer.printString("SELECT * FROM (");
printer.printString(sql.replaceFirst(" FROM ", ", ROWNUMBER() OVER (" + overRequest + ") AS EL_ROWNM FROM ")); // FROM
printer.printString(") AS EL_TEMP WHERE EL_ROWNM > ");
printer.printParameter(DatabaseCall.FIRSTRESULT_FIELD);
}
call.setIgnoreFirstRowSetting(true);
call.setIgnoreMaxResultsSetting(true);
}
private String generateOverRequest(SQLSelectStatement statement) {
StringBuilder overString = new StringBuilder();
for (int i = 0; i < statement.getOrderByExpressions().size(); i++) {
if (i > 0) {
overString.append(" ");
}
overString.append("ORDER BY ");
FunctionExpression expression = (FunctionExpression) statement.getOrderByExpressions().get(i);
for (int i2 = 0; i2 < expression.getChildren().size(); i2++) {
if (i2 > 0) {
overString.append(", ");
}
if (expression.getChildren().get(i2) instanceof QueryKeyExpression) {
QueryKeyExpression qke = (QueryKeyExpression) expression.getChildren().get(i2);
if (statement.requiresAliases()) {
overString.append(qke.getAliasedField().getQualifiedName());
} else {
overString.append(qke.getName());
}
}
}
for (String order : expression.getOperator().getDatabaseStrings()) {
overString.append(order);
}
}
statement.getOrderByExpressions().clear();
return overString.toString();
}
Xanas
[Updated on: Mon, 08 April 2013 13:38] Report message to a moderator
|
|
|
Powered by
FUDForum. Page generated in 0.02402 seconds