Ordering STANDARD or RAW result tables

When the SELECT INTO Table clause defines a permanent table to store the result of a query, any non-trivial column expression in that clause must also declare an alias for the corresponding column in the newly-created result table. To specify that column as a sorting key for the result table, the ORDER BY clause must also reference the same alias, rather than specifying the non-trivial column expression.

For example, in the following nested query, tab56 is the identifier of a result table, and tab56_col0 is an alias for the non-trivial column expression that a subquery in the Projection clause defines. The ORDER BY clause specifies the same subquery as a sorting key, rather than referencing that non-trivial column expression by its alias:

SELECT ( SELECT tab54.tab54_col7 tab56_col0
    FROM tab54
    WHERE (tab54.tab54_col7 = -1423023 )
    ) tab56_col0,
  "" tab56_col1
FROM tab57
WHERE tab57.tab57_col1 == -6296233
ORDER BY  (
    SELECT tab54.tab54_col7 tab56_col0
    FROM tab54
    WHERE (tab54.tab54_col7 = -1423023 )
    ) NULLS FIRST,2 NULLS FIRST
INTO tab56;

Specifying the non-trivial column expression in the ORDER BY clause is acceptable in a normal SELECT statement, but not in an ORDER BY clause that sorts a result table that the SELECT INTO Table clause created. In the example above, the database server returns SQL error -19828.

To avoid this error, the example above must be modified to remove the non-trivial column expression from the ORDER BY clause, replacing that expression with its alias:

SELECT ( SELECT tab54.tab54_col7 tab56_col0
    FROM tab54
    WHERE (tab54.tab54_col7 = -1423023 )
    ) tab56_col0,
  "" tab56_col1
FROM tab57
WHERE tab57.tab57_col1 == -6296233
ORDER BY  
    tab56_col0     -- Substituted alias for column expression in result table)
NULLS FIRST,2 NULLS FIRST
INTO tab56;