How do I SELECT multiple columns as one column

[Robert Stearns]

In a table which has an animal id, its dam id and sire id, I want to see
a combined list of all animal ids. I tried the following, which did not
work, but did not give any error messages:

select regnum from (
select regnum from nullid.angus_in union
select sire_regnum as regnum from nullid.angus_in union
select dam_regnum as regnum from nullid.angus_in
) as t
group by t.regnum having count( * ) > 1;
I have apparently missed something in the SQL, but I don't see what. Any
help, or pointers to the appropriate section of the fine manual would be
greatly appreciated,

[Mark A]

"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

In a table which has an animal id, its dam id and sire id, I want to see
a combined list of all animal ids. I tried the following, which did not
work, but did not give any error messages:

select regnum from (
select regnum from nullid.angus_in union
select sire_regnum as regnum from nullid.angus_in union
select dam_regnum as regnum from nullid.angus_in
) as t
group by t.regnum having count( * ) > 1;
I have apparently missed something in the SQL, but I don't see what. Any
help, or pointers to the appropriate section of the fine manual would be
greatly appreciated,

Could you give us the table DDL and the format of the report you want (with
a couple of examples). I don't understand exactly what you are looking for.

[Robert Stearns]

Mark A wrote:

"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

In a table which has an animal id, its dam id and sire id, I want to see
a combined list of all animal ids. I tried the following, which did not
work, but did not give any error messages:

select regnum from (
select regnum from nullid.angus_in union
select sire_regnum as regnum from nullid.angus_in union
select dam_regnum as regnum from nullid.angus_in
) as t
group by t.regnum having count( * ) > 1;
I have apparently missed something in the SQL, but I don't see what. Any
help, or pointers to the appropriate section of the fine manual would be
greatly appreciated,

Could you give us the table DDL and the format of the report you want (with
a couple of examples). I don't understand exactly what you are looking for.

The ddl for the table is :

-- Start of generated script for halshome.net-DB2inst1-ANIMALS (is)
-- Oct-19-2003 at 15:09:00

-- drop table nullid.angus_in;

CREATE TABLE nullid.angus_in
(tattoo CHARACTER(10) NOT NULL,
tag CHARACTER(5),
sex CHARACTER(1),
birth_date DATE,
regnum BIGINT,
name VARCHAR(50),
dam_tattoo CHARACTER(10),
sire_tattoo CHARACTER(10),
active CHARACTER(1) NOT NULL,
sire_regnum BIGINT,
dam_regnum BIGINT
)
DATA CAPTURE NONE;

The output I want is a single column of all regnums which occur at
least twice in any of the three regnum columns combined.

[Serge Rielau]

Try this:
SELECT T.regnum FROM nullid.angus_in,
TABLE(VALUES (regnum),
(sire_regnum),
(dam_regnum))
AS T(regnum)
GROUP BY T.regnum HAVING COUNT(*) > 1;

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Paul Vernon]

"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

Mark A wrote:

"Robert Stearns" <rs**********@charter.net> wrote in message
news:10*************@corp.supernews.com...

In a table which has an animal id, its dam id and sire id, I want to see
a combined list of all animal ids. I tried the following, which did not
work, but did not give any error messages:

select regnum from (
select regnum from nullid.angus_in union
select sire_regnum as regnum from nullid.angus_in union
select dam_regnum as regnum from nullid.angus_in
) as t
group by t.regnum having count( * ) > 1;

Try

select regnum from (
select regnum from nullid.angus_in UNION ALL
select sire_regnum as regnum from nullid.angus_in UNION ALL
select dam_regnum as regnum from nullid.angus_in
) as t
group by t.regnum having count( * ) > 1;

Regards
Paul Vernon
Business Intelligence, IBM Global Services

[Serge Rielau]

Yes, that is a bit confusing isn't it?

VALUES is a table-constructor.
You pass it a set of tuples and they get magically turned into a table.
You can use it pretty much everywhere, where a query is allowed, such as
in FROM, INSERT (you know that one I presume), as an operand of a set
operation (e.g. to seed a recursion).

Now TABLE is funky. There is a lot of history here
(I know it only second hand I shall add)
It all started with the SQL Standard wanting to allow correlation sideways
SELECT * FROM TCORR, (SELECT * FROM x WHERE x.c1 = TCORR.c1) AS y.
So when a column cannot resolve within it's own context it shall look to
the left in the FROM clause for resolution. Good idea!

However a commercial DBMS already supported this:
SELECT (SELECT 1
FROM TCORR,
(SELECT * FROM c WHERE x.c1 = TCORR.c1) AS y)
FROM TCORR

Where TCORR.c1 would resolve to the outermost FROM. They didn't look
sideways. Doh!

Now, lucky for that DBMS, a compromise was found that allowed the DBMS
to remain compliant with the standard without having to break its'
mighty customers' apps.
The compromise was TABLE. If TABLE preceeds the query which needs a
column resolved then the resolution shall proceed sideways, otherwise
the resolution shall immediately proceed to go up.
The DBMS implemented this feature and in a rare ;-) twist of fate and
politics the word was changed last minute into LATERAL.
DB2 CS (not sure on teh version) aka V2, being a rather young and
impressionable product followed the example of said DBMS because it
turned out to be - not a prince but it's big brother DB2 for MVS.
The rest of the world does pretty much whatever they want.

To make a long story short here is the correct SQL Standard version of
the query (I believe multi-row values is standard, don't quote me on it):
SELECT T.regnum FROM nullid.angus_in as AI,
LATERAL(VALUES (AI.regnum),
(AI.sire_regnum),
(AI.dam_regnum))
AS T(regnum)
GROUP BY T.regnum HAVING COUNT(*) > 1;

as it so happens DB2 Stinger quietly supports the LATERAL keyword :-)

TABLE(foo('hello world')) AS F
is an entirely different animal. That is the SQL Standard way of turning
a multi-set into a TABLE (like a CAST).
Table functions in the SQL Standard are defined to return multi-sets and
not tables (go figure..)

Since table functions are fairly new and they pretty much always want to
correlate their arguments sideways, (and DB2 for MVS didn't have them at
the time) they are allowed to correlate without an extra LATERAL keywords.

G'nite
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Robert Stearns]

Serge Rielau wrote:

Yes, that is a bit confusing isn't it?

VALUES is a table-constructor.
You pass it a set of tuples and they get magically turned into a table.
You can use it pretty much everywhere, where a query is allowed, such as
in FROM, INSERT (you know that one I presume), as an operand of a set
operation (e.g. to seed a recursion).

Now TABLE is funky. There is a lot of history here
(I know it only second hand I shall add)
It all started with the SQL Standard wanting to allow correlation sideways
SELECT * FROM TCORR, (SELECT * FROM x WHERE x.c1 = TCORR.c1) AS y.
So when a column cannot resolve within it's own context it shall look to
the left in the FROM clause for resolution. Good idea!

However a commercial DBMS already supported this:
SELECT (SELECT 1
FROM TCORR,
(SELECT * FROM c WHERE x.c1 = TCORR.c1) AS y)
FROM TCORR

Where TCORR.c1 would resolve to the outermost FROM. They didn't look
sideways. Doh!

Now, lucky for that DBMS, a compromise was found that allowed the DBMS
to remain compliant with the standard without having to break its'
mighty customers' apps.
The compromise was TABLE. If TABLE preceeds the query which needs a
column resolved then the resolution shall proceed sideways, otherwise
the resolution shall immediately proceed to go up.
The DBMS implemented this feature and in a rare ;-) twist of fate and
politics the word was changed last minute into LATERAL.
DB2 CS (not sure on teh version) aka V2, being a rather young and
impressionable product followed the example of said DBMS because it
turned out to be - not a prince but it's big brother DB2 for MVS.
The rest of the world does pretty much whatever they want.

To make a long story short here is the correct SQL Standard version of
the query (I believe multi-row values is standard, don't quote me on it):
SELECT T.regnum FROM nullid.angus_in as AI,
LATERAL(VALUES (AI.regnum),
(AI.sire_regnum),
(AI.dam_regnum))
AS T(regnum)
GROUP BY T.regnum HAVING COUNT(*) > 1;

as it so happens DB2 Stinger quietly supports the LATERAL keyword :-)

TABLE(foo('hello world')) AS F
is an entirely different animal. That is the SQL Standard way of turning
a multi-set into a TABLE (like a CAST).
Table functions in the SQL Standard are defined to return multi-sets and
not tables (go figure..)

Since table functions are fairly new and they pretty much always want to
correlate their arguments sideways, (and DB2 for MVS didn't have them at
the time) they are allowed to correlate without an extra LATERAL keywords.

G'nite
Serge

Thanks for the lessons in sql and its history.

Where do you find things like VALUES general use as opposed to its use
in INSERT? While I haven't read each page of SQL Vol. 1, I would think
that I would have come across some reference to it.

Every once in a while you see a request to turn attributes into rows,
say for cross tabs. While I haven't worked out all the details, a
judicious of this construct would seem to ease that task as well.

Now for a little sleep.

[Paul Vernon]

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:ca**********@hanover.torolab.ibm.com...
[snip]

To make a long story short here is the correct SQL Standard version of
the query (I believe multi-row values is standard, don't quote me on it):
SELECT T.regnum FROM nullid.angus_in as AI,
LATERAL(VALUES (AI.regnum),
(AI.sire_regnum),
(AI.dam_regnum))
AS T(regnum)
GROUP BY T.regnum HAVING COUNT(*) > 1;

Gosh.

So

SELECT T.reg FROM nullid.angus_in AI,
(SELECT AI.regnum FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT AI.sire_regnum FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT AI.dam_regnum FROM SYSIBM.SYSDUMMY1)
AS T(reg)
GROUP BY T.reg HAVING COUNT(*) > 1
;

fails with
SQL0204N "AI.DAM_REGNUM" is an undefined name.
but with the extra TABLE (or in future LATERAL) keyword, the query succeeds.

SELECT T.reg FROM nullid.angus_in AI,
TABLE (SELECT AI.regnum FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT AI.sire_regnum FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT AI.dam_regnum FROM SYSIBM.SYSDUMMY1)
AS T(reg)
GROUP BY T.reg HAVING COUNT(*) > 1
;

Well, I've learnt my new thing for today already and it's not even lunch
time.

For turning cols into rows, I guess the above method might be more efficient
than a 'traditional' method such as below, but it's arguably more obscure.

CREATE TABLE T (id int not null, Y2003 int, Y2004 int, Y2005 int);
insert into T VALUES (1, 50, 60, 70), (2, 55, 65, 77), (3, 40, 20, 40);

-- new
SELECT id, year, value FROM T, TABLE(VALUES (2003, Y2003), (2004, Y2004),
(2005, Y2005)) AS R(year, value)

-- old
SELECT id, year, case when year=2003 then Y2003 when year=2004 then Y2004
when year=2005 then Y2005 end
FROM T, TABLE(VALUES (2003), (2004), (2004)) AS R(year)
;

Regards
Paul Vernon
Business Intelligence, IBM Global Services

[Serge Rielau]

Well, it is a very basic tool for SQL.
Every query you ever do must somehow involve a table.
To produce tables I can think only of:
table functions, tables and various forms of "external tables" such as
Nicknames in DB2 and values.
From there it's transitive closure.
I use VALUES whenever I need to pass a "table" from outside SQL into
a query.
Let's presume you read a cursor into a form for editing.
As common in these cases (.Net, J2EE) you don't want to keep the cursor
open. Instead you build an in memory result set set teh GUI loose on it.
Now hwo to you get the changed data back in?
You can take one row at a time running update statements for each.
Or you cross the boundary to SQL in one shot:

MERGE INTO T USING (VALUES (?, ?), (?, ?), (?, ?)) AS S(pk, val)
ON T.pk = S.pk
WHEN MATCHED THEN UPDATE SET T.val = S.val;

Now, what do you do when you have a lot of rows? You don't want to spell
them all out. The trend is going to this (search for "multi-row-insert"):
http://publib.boulder.ibm.com/cgi-bi...20040216135741

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Pierre Saint-Jacques]

I'm duly impressed and thankful.
This makes reading this newsgroup so good! We do learn a lot from it.
Thanks to Paul and Serge. And yes it's better reading than a whole
thread on market share!!!!
Regards, Pierre

Paul Vernon wrote:

"Serge Rielau" <sr*****@ca.eye-be-em.com> wrote in message
news:ca**********@hanover.torolab.ibm.com...
[snip]

To make a long story short here is the correct SQL Standard version of
the query (I believe multi-row values is standard, don't quote me on it):
SELECT T.regnum FROM nullid.angus_in as AI,
LATERAL(VALUES (AI.regnum),
(AI.sire_regnum),
(AI.dam_regnum))
AS T(regnum)
GROUP BY T.regnum HAVING COUNT(*) > 1;

Gosh.

So

SELECT T.reg FROM nullid.angus_in AI,
(SELECT AI.regnum FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT AI.sire_regnum FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT AI.dam_regnum FROM SYSIBM.SYSDUMMY1)
AS T(reg)
GROUP BY T.reg HAVING COUNT(*) > 1
;

fails with
SQL0204N "AI.DAM_REGNUM" is an undefined name.
but with the extra TABLE (or in future LATERAL) keyword, the query succeeds.

SELECT T.reg FROM nullid.angus_in AI,
TABLE (SELECT AI.regnum FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT AI.sire_regnum FROM SYSIBM.SYSDUMMY1 UNION ALL
SELECT AI.dam_regnum FROM SYSIBM.SYSDUMMY1)
AS T(reg)
GROUP BY T.reg HAVING COUNT(*) > 1
;

Well, I've learnt my new thing for today already and it's not even lunch
time.

For turning cols into rows, I guess the above method might be more efficient
than a 'traditional' method such as below, but it's arguably more obscure.

CREATE TABLE T (id int not null, Y2003 int, Y2004 int, Y2005 int);
insert into T VALUES (1, 50, 60, 70), (2, 55, 65, 77), (3, 40, 20, 40);

-- new
SELECT id, year, value FROM T, TABLE(VALUES (2003, Y2003), (2004, Y2004),
(2005, Y2005)) AS R(year, value)

-- old
SELECT id, year, case when year=2003 then Y2003 when year=2004 then Y2004
when year=2005 then Y2005 end
FROM T, TABLE(VALUES (2003), (2004), (2004)) AS R(year)
;

Regards
Paul Vernon
Business Intelligence, IBM Global Services

--
Pierre Saint-Jacques - Reply to: sesconsjunk at attglobaljunk dot com
Reconstruct address: Remove the two junk and replace at and dot by
their symbols.
IBM DB2 Cerified Solutions Expert - Administration
SES Consultants Inc.

[Robert Stearns]

Serge Rielau wrote:

Well, it is a very basic tool for SQL.
Every query you ever do must somehow involve a table.
To produce tables I can think only of:
table functions, tables and various forms of "external tables" such as
Nicknames in DB2 and values.
From there it's transitive closure.
I use VALUES whenever I need to pass a "table" from outside SQL into
a query.
Let's presume you read a cursor into a form for editing.
As common in these cases (.Net, J2EE) you don't want to keep the cursor
open. Instead you build an in memory result set set teh GUI loose on it.
Now hwo to you get the changed data back in?
You can take one row at a time running update statements for each.
Or you cross the boundary to SQL in one shot:

MERGE INTO T USING (VALUES (?, ?), (?, ?), (?, ?)) AS S(pk, val)
ON T.pk = S.pk
WHEN MATCHED THEN UPDATE SET T.val = S.val;

Now, what do you do when you have a lot of rows? You don't want to spell
them all out. The trend is going to this (search for "multi-row-insert"):
http://publib.boulder.ibm.com/cgi-bi...20040216135741
Cheers
Serge

Thanks for the further education, it's really appreciated.

BTW The manuals at the first URL below are easier for browser users to
read, since they are PDFs. The manuals you referenced are apparently
formatted for a program other than Netscape 7.1 to render, especially
the ASCII art RNF syntax diagrams. For the subjects at hand there should
be little or no difference between the zOS and Linux versions. The
latest information, for 8.1.x may be found at the second URL by drilling
down under Reference / SQL. For stuff added after 8.1 it is the best
resource I have found.

http://www-306.ibm.com/cgi-bin/db2ww...bs.d2w/en_main

http://publib.boulder.ibm.com/infoce...help/index.jsp