1

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, sufficiently large table a row limiter such as FETCH FIRST 2 ROW ONLY, but that seems to be a silly requirement.

So how can I construct rows without an underlying table?

(keywords: tally table; derived table)

Improve this question

2 Answers 2

Reset to default
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
Improve this answer
5

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

Improve this answer

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.