DB2 SQL Y2K 'Puzzler'

[kat...@my-dejanews.com]

My client has the following QMF query, and wants the query
modified, not the table.

SELECT col_1, col_2, col_3,
code, YYMM

FROM table
WHERE code = 'AA' AND
(YYMM = (SELECT MAX(YYMM) FROM table) OR
YYMM = (SELECT MIN(YYMM) FROM table))
ORDER BY YYMM DESC, col_1, col_2

Table has rolling 13 months of data (currently YYMM of 9803 thru 9903).

Query gathers up latest and earliest so that current yymm is
compared to same month, last year.

Needless to say there will be a problem in January of next year.

Note the ORDER BY DESC.

Client uses a Y2K window of yy=60.

I can get this to work:

SELECT '19'||YYMM AS CCYYMM
FROM table
WHERE CODE = 'AA' AND YYMM > '6000'
UNION
SELECT '20'||YYMM AS CCYYMM
FROM table
WHERE CODE = 'AA' AND YYMM < '6000'

I am having a problem 'plugging it in' to the original query. What do I need
to do to get something like the following to work?

SELECT (ORIG COLS), CCYYMM
FROM table
WHERE CODE = 'AA' AND

(CCYYMM = (SELECT MAX(CCYYMM)
FROM
(SELECT '19'||YYMM AS CCYYMM
FROM table
WHERE CODE = 'AA' AND YYMM > '6000'
UNION
SELECT '20'||YYMM AS CCYYMM
FROM table
WHERE CODE = 'AA' AND YYMM < '6000'))
OR
(CCYYMM = (SELECT MIN(CCYYMM)
FROM
(SELECT '19'||YYMM AS CCYYMM
FROM table
WHERE CODE = 'AA' AND YYMM > '6000'
UNION
SELECT '20'||YYMM AS CCYYMM
FROM table
WHERE CODE = 'AA' AND YYMM < '6000'))))
ORDER BY CCYYMM DESC, ETC.

I'm working in SPUFI and getting the usual cryptic error messages. I'm not
sure whether my problem is syntactic, or if this is a futile effort.

Thanks for any help!

-----------== Posted via Deja News, The Discussion Network ==----------
http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

[Ez Paling1]

Sorry to bother you! If you or anyone you know is
interested, please let me know!

DB2 DBA’s CENTRAL JERSEY
(Multiple positons available)

Major financial services firm seeks multiple DB2
DBA’s. There are several different projects running
simultaneously. Some of the work to be done includes:
- Production support
- Infrastructure work
- Documentation
- New Development
- Maintenance
- Disk Management
- Performance Management
- Documenting Existing System

5+ years of DB2 DBA experience.
Full Time positions only, must be US citizen or
authorized to work Fulltime.
Salary u pto $120,000

!!IMMEDIATE INTERVIEW FOR QUALIFIED CANDIDATES
GUARANTEED!!

For more specific information regarding client and
project, contact:
Colton Information Technology
63 Wall Street - Suite 2901
New York, NY 10005
Contact: Esmeldin (EZ) Paling
Voice: (212) 248-9700 Ext. 202
Fax: (212) 509-1633
Ezpa...@aol.com
WWW.tcg1.com

[Chris Wood]

You can not put a UNION in a common table expression in DB2/OS390, but you
can solve the proble with a CASE statement assuming you are at version 5

SELECT '19'||YYMM AS CCYYMM
FROM table

WHERE YYMM > '6000'

UNION
SELECT '20'||YYMM AS CCYYMM
FROM table

WHERE YYMM < '6000'

becomes:

SELECT CASE WHEN YYMM > '6000'
THEN '19'||YYMM
ELSE '20'||YYMM
END AS CCYYMM
FROM table

kat...@my-dejanews.com wrote in article
<7drcmo$58n$1...@nnrp1.dejanews.com>...

[kat...@my-dejanews.com]

I'd like to thank a number of people who made suggestions on how to tackle my
SQL 'puzzler' - particularly Don, Ian, Chris and Jeremy. I posted my
question in several different areas and I think I got useful responses from
all of them - although I wasn't always smart enough to realize it at the
time. ;)

My excuses for being so dense are that I have been away from DB2 SQL for
quite awhile, and that my links to the IBM manuals were pointing to v.4 even
though my client is using v.5. Duh!

Here's what we ended up recommending:

1. Create a new VIEW of the table:

CREATE VIEW new_table_view AS (selecting ALL the columns plus new CCYYMM col)
SELECT col1, col2, YYMM,....
, CASE
WHEN YYMM > '5999'
THEN '19'||YYMM
WHEN YYMM < '6000'
THEN '20'||YYMM
END AS CCYYMM
FROM orig_table

2. Modify the original SQL to use the view and replace references to YYMM
with CCYYMM - thusly:

SELECT col1,
col2,
YYMM,
etc.,
CCYYMM
FROM new_table_view
WHERE
(CCYYMM =
(SELECT MAX(SUBSTR(CCYYMM,1,6)) FROM new_table_view) OR
CCYYMM =
(SELECT MIN(SUBSTR(CCYYMM,1,6)) FROM new_table_view))
ORDER BY CCYYMM DESC, other_cols

Because this is processed by QMF, and there is a FORM involved, it too must
be modified - first to accomodate the new column, then to OMIT the new
column. That way the file where this 'report' is dumped will remain the same.

Again, thanks all of you for the input - all of it was very helpful!
In article <7drcmo$58n$1...@nnrp1.dejanews.com>,