RE: Need help with SQL for derived column and use withsubstitution variables

Couple of options off the top of my head.

-- Option 1

SELECT B.UTCSNM, B.UTCSA2, B.UTCSA3, B.UTCSZP, E.ASANTX, E.ASCICD, A.UTLCID,
A.UTCSID, A.UTSVC, A.UTSSTS, A.UTSSTC, A.UTSSTY, A.UTSSTM, A.UTSSTD,
C.UTACTP, B.UTCSST, D.ABABTX, D.ABCHCD, D.ABACCD, D.ABAECD, D.ABDFCD,
D.ABBPCD,
( ( 1900 + A.UTSSTC * 100 ) + A.UTSSTY ) * 10000 + A.UTSSTM * 100 +
A.UTSSTD
AS CUSTSTARTDATE
FROM CXLIB.UT220AP A INNER JOIN CXLIB.UT200AP B ON B.UTCSID = A.UTCSID INNER
JOIN CXLIB.UT120AP C ON C.UTSVC = A.UTSVC AND C.UTLCID = A.UTLCID INNER
JOIN LXLIB.LMABREP D ON D.ABAUCD = A.UTLCID INNER JOIN LXLIB.XXASREP E
ON
E.ASBPCD = B.UTCSZP
WHERE (A.UTSVC = 'MS')
AND (A.UTSSTS = 'A')
AND NOT (C.UTACTP = 'NC')
AND ((( 1900 + A.UTSSTC * 100 ) + A.UTSSTY ) * 10000 + A.UTSSTM * 100 +
A.UTSSTD) BETWEEN &BeginningDate AND &EndingDate


-- Option 2

WITH CTE1 AS (
SELECT UTLCID, UTCSID, UTSVC, UTSSTS, UTSSTC, UTSSTY, UTSSTM, UTSSTD,
((1900 + UTSSTC * 100) + UTSSTY) * 10000 + UTSSTM * 100 + UTSSTD AS
CUSTSTARTDATE
FROM CXLIB.UT220AP
WHERE UTSVC = 'MS' AND UTSSTS = 'A')
SELECT B.UTCSNM, B.UTCSA2, B.UTCSA3, B.UTCSZP, E.ASANTX, E.ASCICD, A.UTLCID,
A.UTCSID, A.UTSVC, A.UTSSTS, A.UTSSTC, A.UTSSTY, A.UTSSTM, A.UTSSTD,
C.UTACTP, B.UTCSST, D.ABABTX, D.ABCHCD, D.ABACCD, D.ABAECD, D.ABDFCD,
D.ABBPCD,
( ( 1900 + A.UTSSTC * 100 ) + A.UTSSTY ) * 10000 + A.UTSSTM * 100 +
A.UTSSTD
AS CUSTSTARTDATE
FROM CTE1 A INNER JOIN CXLIB.UT200AP B ON B.UTCSID = A.UTCSID INNER
JOIN CXLIB.UT120AP C ON C.UTSVC = A.UTSVC AND C.UTLCID = A.UTLCID INNER
JOIN LXLIB.LMABREP D ON D.ABAUCD = A.UTLCID INNER JOIN LXLIB.XXASREP E
ON
E.ASBPCD = B.UTCSZP
WHERE CUSTSTARTDATE BETWEEN &BeginningDate AND &EndingDate AND
NOT (C.UTACTP = 'NC')

There are of course many variations on option 2, like putting the date
selection in the CTE1. And/or having CTE2 for UT120AP selection, leaving
the SELECT with JOIN criteria only.

HTH, Elvis

Celebrating 10-Years of SQL Performance Excellence on IBM i5/OS and OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Need help with SQL for derived column and use withsubstitution
variables

The following is a query a user is trying to create for an interactive query
in QMF for Windows and/or DB2 Query Manager. The problems they encounter
are:

- Conversion errors between columns containing input from substitution
variables and the derived column in the SELECT statement (this happens if
the query is run as written here)

OR

- taking the single quotes off the derived column name as used in the WHERE
clause causes a "Column not found in tables" error.

As you can probably see what the user/developer is trying to do is take four
columns that are zoned decimal and make a column that essentially takes the
data from those separate columns and makes it one and then use that column
to discriminate rows based on the variable input from the end user when the
query is run. For the end user this means two variables to enter rather
than eight.

Any help appreciated. Thanks, Dave

SELECT B.UTCSNM, B.UTCSA2, B.UTCSA3, B.UTCSZP, E.ASANTX, E.ASCICD, A.UTLCID,
A.UTCSID, A.UTSVC, A.UTSSTS, A.UTSSTC, A.UTSSTY, A.UTSSTM, A.UTSSTD,
C.UTACTP, B.UTCSST, D.ABABTX, D.ABCHCD, D.ABACCD, D.ABAECD, D.ABDFCD,
D.ABBPCD,
( ( 1900 + A.UTSSTC * 100 ) + A.UTSSTY ) * 10000 + A.UTSSTM * 100 +
A.UTSSTD
AS CUSTSTARTDATE
FROM CXLIB.UT220AP A INNER JOIN CXLIB.UT200AP B ON B.UTCSID = A.UTCSID INNER
JOIN CXLIB.UT120AP C ON C.UTSVC = A.UTSVC AND C.UTLCID = A.UTLCID INNER
JOIN LXLIB.LMABREP D ON D.ABAUCD = A.UTLCID INNER JOIN LXLIB.XXASREP E
ON
E.ASBPCD = B.UTCSZP
WHERE (A.UTSVC = 'MS')
AND (A.UTSSTS = 'A')
AND NOT (C.UTACTP = 'NC')
AND ('CUSTSTARTDATE' >= &BeginningDate)
AND ('CUSTSTARTDATE' <= &EndingDate)