In T-SQL, I can execute the following:
SELECT * FROM (SELECT 1 AS n UNION ALL SELECT 2 AS n) AS t;
In IBM DB2 SQL, this fails with:
[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0199 - Keyword UNION not expected. Valid tokens: , FROM INTO.
SELECT statement seems to require a
FROM clause. Indeed, a reductive SQL statement such as:
SELECT 1 AS n
Also errors with:
[IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token was not valid. Valid tokens: , FROM INTO.
There is an UNNEST function which can create rows from an array, but it seems the array must already be represented by something like a variable or parameter.
I understand I could work around this by doing a
SELECT from an arbitrary table with an impossible criterion such as 1 = 0, but that seems to be a silly requirement.
So how can I construct rows without an underlying table?
(keywords: tally table; derived table)