prototype tables

Frank Swarbrick

prototype tables
With DB2 for LUW you can use a common table expression to 'prototype' a table without needing to create it:

with
temp(a, b) as (
values (1, 'A')
, (2, 'B')
, (3, 'C')
)
select a
, b
from temp;

Results:
A B
----------- -
1 A
2 B
3 C

3 record(s) selected.

I know that DB2 for z/OS does not support the "values-clause" in a fullselect. Is there any way to do something similar to this in z/OS?

Thanks,
Frank



--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403


>>>

The information contained in this electronic communication and any document attached hereto or transmitted herewith is confidential and intended for the exclusive use of the individual or entity named above. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any examination, use, dissemination, distribution or copying of this communication or any part thereof is strictly prohibited. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy this communication. Thank you.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Frank Swarbrick

Re: prototype tables
(in response to Frank Swarbrick)
>>> On 12/1/2009 at 4:53 PM, in message <[login to unmask email]>,
Frank Swarbrick <[login to unmask email]> wrote:
> With DB2 for LUW you can use a common table expression to 'prototype' a table
> without needing to create it:
>
> with
> temp(a, b) as (
> values (1, 'A')
> , (2, 'B')
> , (3, 'C')
> )
> select a
> , b
> from temp;
>
> Results:
> A B
> ----------- -
> 1 A
> 2 B
> 3 C
>
> 3 record(s) selected.
>
> I know that DB2 for z/OS does not support the "values-clause" in a
> fullselect. Is there any way to do something similar to this in z/OS?

If I can answer my own question... I found the answer in the DB2 for LUW docs, which state:
"A values-clause that is composed of n specifications of values-row, RE1 to REn, where n is greater than 1, is equivalent to:
RE1 UNION ALL RE2 ... UNION ALL REn"

And thus...
with
temp(a,b) as (
select 1, 'A'
from sysibm.sysdummy1
union all
select 2, 'B'
from sysibm.sysdummy1
union all
select 3, 'C'
from sysibm.sysdummy1
)
select *
from temp;

Not pretty, but it works.

I wonder why z/OS has never added the values clause. A rather useful thing to have (pretty much eliminates the need for the sysibm.sysdummy1 view).
Ah well.

Frank
--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403




The information contained in this electronic communication and any document attached hereto or transmitted herewith is confidential and intended for the exclusive use of the individual or entity named above. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any examination, use, dissemination, distribution or copying of this communication or any part thereof is strictly prohibited. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy this communication. Thank you.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

David Simpson

Re: prototype tables
(in response to Frank Swarbrick)
How about this.... it's quirky but it works:

with
temp(a, b) as (
select 1,'A' from sysibm.sysdummy1
union all
select 2,'B' from sysibm.sysdummy1
union all
select 3,'C' from sysibm.sysdummy1
)
select a
, b
from temp;

________________________________________________________________________
______
David Simpson | Senior Technical Advisor | Themis Education
123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
[login to unmask email]
For more information about Themis, visit www.themisinc.com

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Frank
Swarbrick
Sent: Tuesday, December 01, 2009 5:54 PM
To: [login to unmask email]
Subject: [DB2-L] prototype tables

With DB2 for LUW you can use a common table expression to 'prototype' a
table without needing to create it:

with
temp(a, b) as (
values (1, 'A')
, (2, 'B')
, (3, 'C')
)
select a
, b
from temp;

Results:
A B
----------- -
1 A
2 B
3 C

3 record(s) selected.

I know that DB2 for z/OS does not support the "values-clause" in a
fullselect. Is there any way to do something similar to this in z/OS?

Thanks,
Frank



--

Frank Swarbrick
Applications Architect - Mainframe Applications Development
FirstBank Data Corporation - Lakewood, CO USA
P: 303-235-1403


>>>

The information contained in this electronic communication and any
document attached hereto or transmitted herewith is confidential and
intended for the exclusive use of the individual or entity named above.
If the reader of this message is not the intended recipient or the
employee or agent responsible for delivering it to the intended
recipient, you are hereby notified that any examination, use,
dissemination, distribution or copying of this communication or any part
thereof is strictly prohibited. If you have received this communication
in error, please immediately notify the sender by reply e-mail and
destroy this communication. Thank you.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 *
http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/db2-videos.html has hundreds of video presentations!
Did you miss out on attending an IDUG conference?
Many of the presentations were recorded and are available on our
website!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's DB2-L

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Frank Swarbrick

Re: prototype tables
(in response to Bill Cummins)
That does it.
Thanks!
Frank

On 12/1/2009 at 7:08 PM, David Simpson <[login to unmask email]> wrote:
> How about this.... it's quirky but it works:
>
> with
> temp(a, b) as (
> select 1,'A' from sysibm.sysdummy1
> union all
> select 2,'B' from sysibm.sysdummy1
> union all
> select 3,'C' from sysibm.sysdummy1
> )
> select a
> , b
> from temp;
>
> ________________________________________________________________________
> ______
> David Simpson | Senior Technical Advisor | Themis Education
> 123 South Avenue E, Westfield NJ, 07090 | phone 908-233-8900 |
> [login to unmask email]
> For more information about Themis, visit www.themisinc.com
>
> -----Original Message-----
> From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Frank
> Swarbrick
> Sent: Tuesday, December 01, 2009 5:54 PM
> To: [login to unmask email]
> Subject: [DB2-L] prototype tables
>
> With DB2 for LUW you can use a common table expression to 'prototype' a
> table without needing to create it:
>
> with
> temp(a, b) as (
> values (1, 'A')
> , (2, 'B')
> , (3, 'C')
> )
> select a
> , b
> from temp;
>
> Results:
> A B
> ----------- -
> 1 A
> 2 B
> 3 C
>
> 3 record(s) selected.
>
> I know that DB2 for z/OS does not support the "values-clause" in a
> fullselect. Is there any way to do something similar to this in z/OS?
>
> Thanks,
> Frank
>
>

>>>

The information contained in this electronic communication and any document attached hereto or transmitted herewith is confidential and intended for the exclusive use of the individual or entity named above. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering it to the intended recipient, you are hereby notified that any examination, use, dissemination, distribution or copying of this communication or any part thereof is strictly prohibited. If you have received this communication in error, please immediately notify the sender by reply e-mail and destroy this communication. Thank you.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Bill Cummins

Re: prototype tables
(in response to David Simpson)
Or maybe
WITH temp (seedval,seedChar) AS (
Select 65, chr(65) from sysibm.sysdummy1
UNION ALL
SELECT seedval + 1, chr(seedval+1) FROM temp
WHERE seedval + 1 < 140
)
select * from temp

>With DB2 for LUW you can use a common table expression to 'prototype' a
>table without needing to create it:
>
>with temp(a, b) as (
> values (1, 'A')
> , (2, 'B')
> , (3, 'C')
> )
>select a, b from temp;
>
>Results:
>A B
>----------- -
> 1 A
> 2 B
> 3 C
>
> 3 record(s) selected.
>
>I know that DB2 for z/OS does not support the "values-clause" in a
>fullselect. Is there any way to do something similar to this in z/OS?
>
>Thanks,
>Frank
>

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/events/index.html is your DB2 Events calendar! RUG meetings,
Webcasts, Conferences- what is going on next?
RUG leaders- get your events on the calendar today!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L