473,326 Members | 2,148 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Generating rows on the fly

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
9 4957
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
> > 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
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
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
--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
--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
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
--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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Philippe C. Martin | last post by:
Hi, I wish to use an easy way to generate reports from wxPython and feel wxHtmlEasyPrinting could be a good solution. I now need to generate the HTML wxHtmlEasyPrinting can print: I need to...
0
by: smrtalec | last post by:
I am trying to scavange a example script I found on the web. The aubroutine is below. Basically I want to extract data from a sql db then generate a table of values on the fly. The script below...
4
by: sidd | last post by:
Hi all, please see if some one can suggest a better approach for this. i need to move 5 million rows from one table to another. LoanID is the only clumn in Source table Source table structure...
4
by: Mark | last post by:
BEGINNER QUESTION I have a table which has a compound primary key consisting of two columns. One of these columns is a foreign key which is generated in another table by an identity. I want...
10
by: Al Christoph | last post by:
Please forgive me if this is the wrong place to post this. The last place I posted got me a fairly rude response. I guess vb.db people just don't want to think about XML as database. At any rate,...
8
by: matt | last post by:
hello, can anyone speak to some of the common or preferred methods for building Excel .XLS files, programmatically thru the .NET framework? i have an intranet app that needs to generate &...
6
by: Lloyd Sheen | last post by:
Perhaps I have missed something but what I would like to do is have a more "controlled" method of generating HTML from a web service. I can create items using HtmlTable, HtmlTableRow, and...
2
shahjapan
by: shahjapan | last post by:
Hello, I am creating a Office Word 2003 Addin. in which I am using Mail Merge through C# coding. I required to generate a Temporary Data Source which contains a complex structure. Can...
1
geon
by: geon | last post by:
Hi! I have a table I'm using as a stack to pop rows from. (The data in the stack is precomputed for efficiency and reliability.) There are a lot of duplicate rows in this table (hundreds or...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.