Charles, I guess the TABLE keyword isn't required. I've been using this since it was added at V6R1. Much better than using FROM sysibm/sysdummy1 IMHO.
SELECT Numbers, Alpha, Timestamp(Date('7/05/2016'),Time('01:00 PM')) as Now FROM (Values (1, 'A'),(2, 'B')) as Columns(Numbers, Alpha) WHERE Numbers = 1
--------------------------------------------
On Tue, 7/5/16, Charles Wilt <charles.wilt@xxxxxxxxx> wrote:
Subject: Building a table on the fly - table value constructor
To: "Midrange Systems Technical Discussion" <midrange-l@xxxxxxxxxxxx>
Date: Tuesday, July 5, 2016, 12:46 PM
All,
Every once and a while, I need to use the following
technique and every
time it takes me a few minutes to find the right syntax.
The idea is to build a temporary table in memory, from text
data rather
than actually inserting it into an actual (temporary)
table.
There's two valid syntaxes
WITH X(foo, bar, baz) AS (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) SELECT * FROM X;
or
SELECT * FROM TABLE (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) X(foo, bar, baz);
This "table value constructor" was added to the SQL99
standards. Not sure
when it made it to DB2 for i; perhaps v5r4 but it certainly
works in 7.1.
But DB2 for LUW has apparently had it since v7.2.8.
Hopefully, someone else will find this useful and that in
the future I'll
be able to find this post again quickly :)
Charles
--
This is the Midrange Systems Technical Discussion
(MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/midrange-l.
Please contact support@xxxxxxxxxxxx
for any subscription related questions.
midrange.com
