By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,779 Members | 1,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,779 IT Pros & Developers. It's quick & easy.

Generating rows on the fly

P: n/a
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.

Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
YONETANI Tomokazu wrote:
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);


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
Nov 12 '05 #2

P: n/a
> > 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);
That is standardized SQL for many years already. See clause 7.3,

<table value constructor> in the SQL99 standard.


Well, I wasn't very clear about what I was trying to ask(though I don't
have SQL99 standard at hand); I was worrying about the "TABLE" keyword
before the sub-select(I originally found the example in the description
of MERGE statement in Information Center) and thought that it was
something special to assign column names to generated tables. Since
VALUES() by itself doesn't allow column names within the parens, I was
believing that it can't be used by itself to generate rows with column
names.
After posting the question, I found that the TABLE keyword is optional,
and it's the same thing as:

.... FROM (VALUES(0, 1, 2)) AS X(foo,bar,baz)

and it's the column list following X that was essential.

Thanks.

Nov 12 '05 #3

P: n/a
YONETANI Tomokazu wrote:
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);


That is standardized SQL for many years already. See clause 7.3,


<table
value constructor> in the SQL99 standard.

Well, I wasn't very clear about what I was trying to ask(though I don't
have SQL99 standard at hand); I was worrying about the "TABLE" keyword
before the sub-select(I originally found the example in the description
of MERGE statement in Information Center) and thought that it was
something special to assign column names to generated tables. Since
VALUES() by itself doesn't allow column names within the parens, I was
believing that it can't be used by itself to generate rows with column
names.
After posting the question, I found that the TABLE keyword is optional,
and it's the same thing as:

... FROM (VALUES(0, 1, 2)) AS X(foo,bar,baz)

and it's the column list following X that was essential.

Thanks.

The TABLE keyword has a lot of history originating in DB2 for zOS I believe.
The SQL Standard equivalent is "LATERAL".
LATERAL allows a subquery to reference lateral" correlations. E.g.:

SELECT * FROM T, LATERAL(SELECT 1 FROM S WHERE S.PK = T.PK) AS X
In DB2 V8.2 FOR LUW you can specify LATERAL in place of TABLE in this
case (hasn't found it's way into the docs yet)

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #4

P: n/a
I do not remember LATERAL as a keyword, but you can write:

SELECT *
FROM T, (SELECT 1 FROM S WHERE S.PK = T.PK) AS X(i);

as long as the subquery is a scalar. And "TABLE <base table name>" was
allowed as a substitute for "(SELECT * FROM Foobar)"

Nov 12 '05 #5

P: n/a
--CELKO-- wrote:
I do not remember LATERAL as a keyword, but you can write:

SELECT *
FROM T, (SELECT 1 FROM S WHERE S.PK = T.PK) AS X(i);

as long as the subquery is a scalar. And "TABLE <base table name>" was
allowed as a substitute for "(SELECT * FROM Foobar)"

To the best of my knowledge this is not SQL Standad compliant.
Let me illustrate:
SELECT * FROM T
WHERE EXISTS(SELECT *
FROM T,
(SELECT 1 FROM S WHERE S.PK = T.PK) AS X(i)
WHERE T.C1 = X.I)

According to the SQL Standard T.PK resolves to the first "parent" with
T.PK. The reference to T within the EXISTS is an "Uncle" to the WHERE
clause S.PK = T.PK in a manner.
So the SQL standard requires LATERAL() for lateral correlation.

According to teh bedtime stories I heard :-):
The original clause proposed to the standard was TABLE(), but it was
changed last minute (and after DB2 had commited its code to TABLE()) to
LATERAL() which is why DB2 ended up with TABLE.
One might argue that was fair punishment since IBM pushed the need for
the keyword to begin with. Reason being that DB2 for zOS had the above
semantics as precedence and in thso edays it was hard to put something
into teh standard that contradicted what DB2 for zOS had already shipped.
Since those semantics are not evil or bad and there was reasonable way
to get to what was desired (resolve laterally first) this keyword was
born as a compromise (and Oracle's SQL rep was not happy ;-)

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

P: n/a
--CELKO-- wrote:
I do not remember LATERAL as a keyword, but you can write:


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

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #7

P: n/a
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?
According to the bedtime stories I heard ... <<


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 :)

Nov 12 '05 #8

P: n/a
--CELKO-- wrote:
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) ; This is, strictly speaking, illegal. The subquery cannot see T1.PK.
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.
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?

According to the bedtime stories I heard ... <<

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 :)

I wonder whether there is a market for a story book on the SQL Standard,
or standards in general...

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #9

P: n/a
Serge Rielau wrote:
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 :)

I wonder whether there is a market for a story book on the SQL Standard,
or standards in general...


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

--
Knut Stolze
Information Integration
IBM Germany / University of Jena
Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.