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.
A 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)