Eclipse Community Forums
Forum Search:

Search      Help    Register    Login    Home
Home » Eclipse Projects » EclipseLink » Problem with Class DB2Platform function printSQLSelectStatement
Problem with Class DB2Platform function printSQLSelectStatement [message #1027847] Wed, 27 March 2013 13:46 Go to next message
Xanas Aurel is currently offline 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: Prblem with Class DB2Platform function printSQLSelectStatement [message #1028747 is a reply to message #1027847] Thu, 28 March 2013 17:26 Go to previous messageGo to next message
Chris Delahunt is currently offline Chris Delahunt
Messages: 1034
Registered: July 2009
Senior Member
How are you generating the query and what are the versions of the database and driver you are using? It does look like a bug, so please file one so the form can be fixed. A workaround and the code to change if you wanted to fix it yourself are mentioned in the post here https://forums.oracle.com/forums/thread.jspa?threadID=906464&tstart=180

Best regards
Chris

[Updated on: Thu, 28 March 2013 17:34]

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 Go to previous message
Xanas Aurel is currently offline 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

Previous Topic:IN query with composite primary key bug.
Next Topic:[Closed] EclipseLink.Examples.student.web JPARSApplication cannot be instantiated
Goto Forum:
  


Current Time: Sun Oct 26 06:09:44 GMT 2014

Powered by FUDForum. Page generated in 0.02918 seconds
.:: Contact :: Home ::.

Powered by: FUDforum 3.0.2.
Copyright ©2001-2010 FUDforum Bulletin Board Software