Hi.
You can use the following SQL to construct rows with column names
on the fly, rather than from an existing table like sysibm.sysdummy1:
SELECT * FROM TABLE (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) X(foo, bar, baz);
or
WITH X(foo, bar, baz) AS (
VALUES (0, 1, 2), (3, 4, 5), (6, 7, 8)
) SELECT * FROM X;

As far as I tested, this construct is available on 7.2.8 and onwards.
This is sometimes useful, for example in MERGE INTO statement, or
when you don't want to create a table with a random name just to
prepare a few rows like above.

My question is, is this DB2-specific syntax, or is it(going to be)
a part of the standard in the future? I tried on PostgreSQL, but it
doesn't even accept the following SQL(to generate three columns
without column names):
VALUES (0, 1, 2);

Thanks in advance.

YONETANI Tomokazu wrote:

That is standardized SQL for many years already. See clause 7.3, <table
value constructor> in the SQL99 standard.

As to the reasons why PostgreSQL doesn't support this syntax, you might want
to ask the developers there.

p.s: The SYSIBM.SYSDUMMY1 catalog view is actually defined in such a way.
"create view sysibm.sysdummy1 (ibmreqd) as values (char('Y'))" (Extracted
using "SELECT text FROM syscat.views WHERE viewname = 'SYSDUMMY1'".)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

--CELKO-- wrote:

Have a look at Subclause 7.6, <table reference> in SQL2003. This is feature
F491.

--
Knut Stolze
Information Integration
IBM Germany / University of Jena

I am honestly lost on this one. I would expand and qualify that example
like this:

SELECT * FROM T
WHERE EXISTS
(SELECT *
FROM T AS T1
CROSS JOIN
(SELECT 1
FROM S
WHERE S.PK = T1.PK)
AS X(i)
WHERE T1.C1 = X.i) ;

I just follow the usual scoping rules to find the innermost copy of
table T. if I want some strange realtionships, I cn qualify the copies
of T

SELECT * FROM T AS T0
WHERE EXISTS
(SELECT *
FROM T AS T1
CROSS JOIN
(SELECT 1
FROM S
WHERE S.PK = T0.PK)
AS X(i)
WHERE T1.C1 = X.i) ;

What does LATERAL do that is different?

The keyword COALESCE() instead of IBM's VALUE() is another story like
that. Someone at the meeting had a pocket thesaurus and pulled that
one out of list of things like "melt", "fuse", "meld", etc. It ws the
least likely to be a column or table name, so we went with it

--CELKO-- wrote:
In DB2 you will get a resolution error.
If, however, you wrap LATERAL (or TABLE) around the query:
SELECT * FROM T
WHERE EXISTS
(SELECT *
FROM T AS T1
CROSS JOIN
LATERAL(SELECT 1
FROM S
WHERE S.PK = T1.PK)
AS X(i)
WHERE T1.C1 = X.i) ;

T1.PK magically moves into view.

or standards in general...

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

Serge Rielau wrote:

My experience is that people are indeed interested in this kind of
stories. ;-)

--
Knut Stolze
Information Integration
IBM Germany / University of Jena