3.6 Database preview - DB Sorter errror (Teradata)

KNIME Extensions
1

The DB Sorter doesn’t work with Teradata, because ORDER BY is not allowed in subqueries in Teradata.

The generated SQL is:

SELECT *
FROM
    (SELECT *
    FROM
        (SELECT * FROM "prod_exp_dl_mor"."rda_budget"
        ) tempTable_1476382006016265540
    ORDER BY
        "store_number",
        "fin_year",
        "week_number"
    ) AS "tempTable_5557327916575954878"
WHERE
    1 = 0;

which throws

java.sql.SQLException: [Teradata Database] [TeraJDBC 15.10.00.35] [Error 3706] [SQLState 42000] Syntax error: ORDER BY is not allowed in subqueries.

(I’m not sure why there is WHERE 1=0 clause, which ensures nothing is returned).

If I refactor the query like this, it will return the required result:

SELECT *
FROM
    (SELECT *
    FROM
        (SELECT * FROM "prod_exp_dl_mor"."rda_budget"
        ) tempTable_1476382006016265540
    ) AS "tempTable_5557327916575954878"
ORDER BY
    "store_number",
    "fin_year",
    "week_number"
WHERE
    1 = 1;
1 Like
2

Thanks for the hint. We are on it and let you know as soon as we have a solution for this. For now, using the Sorter in the end might be the best option to overcome this issue.