0

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)

3

DB2 for i5 V5R4 does not support the VALUES statement as a table reference, so examples provided in another answer won't work in that version. However, all versions of DB2 offer a single row pseudo-table SYSIBM.SYSDUMMY1, which can be used to generate rows:

SELECT 1 AS n FROM SYSIBM.SYSDUMMY1
UNION ALL 
SELECT 2 AS n FROM SYSIBM.SYSDUMMY1
3

Platform and version are important when asking about Db2...

In this case it appears you're running Db2 for IBM i (iSeries, AS/400)

What you are looking for is known in the SQL standards as table value constructor which looks like VALUES (<row1>),(<row2>),(...)

On Db2 for i (and LUW I believe) the following are a couple of ways...

-- Using a Common Table Expression (CTE)
WITH X(foo, bar, baz) AS (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) SELECT * FROM X;


-- Using a Nested Table Expression (NTE)
SELECT * FROM TABLE (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) X(foo, bar, baz);

However, you appear to be using an older version of Db2 for i (v5?) I don't recall if that version supports table value constructors...

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.