[DB2 V7 z/OS] SQL problem - generated dates to be joined further

Mark Vickers

[DB2 V7 z/OS] SQL problem - generated dates to be joined further
We are still on v7 so I cannot use recursive SQL which seems to be better
solution for this.

Problem:
for the next 7 days, we need to get a list of any alternate delivery
routings and that resultset
needs to be joined to more tables, so we either need a massive join or I
was thinking along
the lines of using a created global temp table and or stored procedure.
This is going to be called thousands of times per day.

This is what we have so far for the first part of the solution but just
wondered if anyone can suggest a better method:

Displaying result for:
---------------------
select dayofweek_iso(z.dt), z.dt, value(A.ALT, ' ')
from (
select current_date as dt from sysibm.sysdummy1
union all
select current_date + 1 day as dt from sysibm.sysdummy1
union all
select current_date + 2 day as dt from sysibm.sysdummy1
union all
select current_date + 3 day as dt from sysibm.sysdummy1
union all
select current_date + 4 day as dt from sysibm.sysdummy1
union all
select current_date + 5 day as dt from sysibm.sysdummy1
union all
select current_date + 6 day as dt from sysibm.sysdummy1
) z
left outer join WW44G.WCSSTART A on A.DTE = dt --(Alternate Routing
Table)
with ur

1 DT 3
----------- ---------- ----
4 2007-12-27
5 2007-12-28
6 2007-12-29
7 2007-12-30
1 2007-12-31 2
2 2008-01-01 3
3 2008-01-02

7 Row(s) affected

THE TWO DAYS WHICH HAVE ALTERNATE ROUTING HAVE THE VALUES IN COLUMN 3.
THIS WILL BE JOINED WITH A FEW OTHER TABLES TO PRODUCE ROUTING INFO FOR
EACH DELIVERY.

thanks,
Mark.



This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

M. Khalid Khan

Re: [DB2 V7 z/OS] SQL problem - generated dates to be joined further
(in response to Mark Vickers)
Why not simplify the SQL as follows:

select dayofweek_iso(DTE), DTE, ALT
from WW44G.WCSSTART
where DTE between current_date and (current_date + 6 day)
and ALT is not null ;

This will return only the rows with alternate routes in the date range.
Unless there is any specific need for a temp table, this can very well be
joined to other tables.

Hope this helps
Khalid







[login to unmask email]
Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>
12/27/2007 03:01 PM
Please respond to
"DB2 Database Discussion list at IDUG" <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] [DB2 V7 z/OS] SQL problem - generated dates to be joined
further







We are still on v7 so I cannot use recursive SQL which seems to be better
solution for this.

Problem:
for the next 7 days, we need to get a list of any alternate delivery
routings and that resultset
needs to be joined to more tables, so we either need a massive join or I
was thinking along
the lines of using a created global temp table and or stored procedure.
This is going to be called thousands of times per day.

This is what we have so far for the first part of the solution but just
wondered if anyone can suggest a better method:

Displaying result for:
---------------------
select dayofweek_iso(z.dt), z.dt, value(A.ALT, ' ')
from (
select current_date as dt from sysibm.sysdummy1
union all
select current_date + 1 day as dt from sysibm.sysdummy1
union all
select current_date + 2 day as dt from sysibm.sysdummy1
union all
select current_date + 3 day as dt from sysibm.sysdummy1
union all
select current_date + 4 day as dt from sysibm.sysdummy1
union all
select current_date + 5 day as dt from sysibm.sysdummy1
union all
select current_date + 6 day as dt from sysibm.sysdummy1
) z
left outer join WW44G.WCSSTART A on A.DTE = dt --(Alternate Routing
Table)
with ur

1 DT 3
----------- ---------- ----
4 2007-12-27
5 2007-12-28
6 2007-12-29
7 2007-12-30
1 2007-12-31 2
2 2008-01-01 3
3 2008-01-02

7 Row(s) affected

THE TWO DAYS WHICH HAVE ALTERNATE ROUTING HAVE THE VALUES IN COLUMN 3.
THIS WILL BE JOINED WITH A FEW OTHER TABLES TO PRODUCE ROUTING INFO FOR
EACH DELIVERY.

thanks,
Mark.


This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org under
the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services


**********

The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma; or (972)766-6900 in Texas.

**********

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mark Vickers

Re: [DB2 V7 z/OS] SQL problem - generated dates to be joined further
(in response to M. Khalid Khan)
Thanks Khalid, but the WCSSTART table only contains dates where alternate
routing is used, so this returns only those rows:
1 DTE ALT
----------- ---------- ----
1 2007-12-31 2
2 2008-01-01 3

whereas we need the next 7 days, dates and either the alternate routing
route number or a blank to specify normal routing:
DOW DTE ALT
------ ---------- ----
4 2007-12-27
5 2007-12-28
6 2007-12-29
7 2007-12-30
1 2007-12-31 2
2 2008-01-01 3
3 2008-01-02





[login to unmask email]
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/27/2007 03:35 PM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2 V7 z/OS] SQL problem - generated dates to be joined further







Why not simplify the SQL as follows:

select dayofweek_iso(DTE), DTE, ALT
from WW44G.WCSSTART
where DTE between current_date and (current_date + 6 day)
and ALT is not null ;

This will return only the rows with alternate routes in the date range.
Unless there is any specific need for a temp table, this can very well be
joined to other tables.

Hope this helps
Khalid






[login to unmask email]
Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>
12/27/2007 03:01 PM

Please respond to
"DB2 Database Discussion list at IDUG" <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] [DB2 V7 z/OS] SQL problem - generated dates to be joined
further









We are still on v7 so I cannot use recursive SQL which seems to be better
solution for this.

Problem:
for the next 7 days, we need to get a list of any alternate delivery
routings and that resultset
needs to be joined to more tables, so we either need a massive join or I
was thinking along
the lines of using a created global temp table and or stored procedure.
This is going to be called thousands of times per day.

This is what we have so far for the first part of the solution but just
wondered if anyone can suggest a better method:

Displaying result for:
---------------------
select dayofweek_iso(z.dt), z.dt, value(A.ALT, ' ')
from (
select current_date as dt from sysibm.sysdummy1
union all
select current_date + 1 day as dt from sysibm.sysdummy1
union all
select current_date + 2 day as dt from sysibm.sysdummy1
union all
select current_date + 3 day as dt from sysibm.sysdummy1
union all
select current_date + 4 day as dt from sysibm.sysdummy1
union all
select current_date + 5 day as dt from sysibm.sysdummy1
union all
select current_date + 6 day as dt from sysibm.sysdummy1
) z
left outer join WW44G.WCSSTART A on A.DTE = dt --(Alternate Routing
Table)
with ur

1 DT 3
----------- ---------- ----
4 2007-12-27
5 2007-12-28
6 2007-12-29
7 2007-12-30
1 2007-12-31 2
2 2008-01-01 3
3 2008-01-02

7 Row(s) affected

THE TWO DAYS WHICH HAVE ALTERNATE ROUTING HAVE THE VALUES IN COLUMN 3.
THIS WILL BE JOINED WITH A FEW OTHER TABLES TO PRODUCE ROUTING INFO FOR
EACH DELIVERY.

thanks,
Mark.

This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org under
the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services
**********
The information contained in this communication is confidential, private,
proprietary, or otherwise privileged and is intended only for the use of
the addressee. Unauthorized use, disclosure, distribution or copying is
strictly prohibited and may be unlawful. If you have received this
communication in error, please notify the sender immediately at
(312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in
Oklahoma; or (972)766-6900 in Texas.
**********

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org under
the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services




This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

M. Khalid Khan

Re: [DB2 V7 z/OS] SQL problem - generated dates to be joined further
(in response to Mark Vickers)
That requirement makes it a little more complicated. If it's a program
producing this report, it can supply the missing dates in the final output
by sorting it on date. If it has to be a SQL only solution, I'd create a
one column table with the seven dates in it and add the following to the
main SQL:

UNION ALL
SELECT dayofweek_iso(TMP_DT), TMP_DT, ' '
FROM TEMP_TAB A
WHERE NOT EXISTS ( SELECT 1 FROM WW44G.WCSSTART WHERE DTE = A.TMP_DT )

HTH
Khalid




[login to unmask email]
Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>
12/28/2007 10:28 AM
Please respond to
"DB2 Database Discussion list at IDUG" <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] [DB2 V7 z/OS] SQL problem - generated dates to be joined
further







Thanks Khalid, but the WCSSTART table only contains dates where alternate
routing is used, so this returns only those rows:
1 DTE ALT
----------- ---------- ----
1 2007-12-31 2
2 2008-01-01 3

whereas we need the next 7 days, dates and either the alternate routing
route number or a blank to specify normal routing:
DOW DTE ALT
------ ---------- ----
4 2007-12-27
5 2007-12-28
6 2007-12-29
7 2007-12-30
1 2007-12-31 2
2 2008-01-01 3
3 2008-01-02



**********

The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma; or (972)766-6900 in Texas.

**********

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms