IBM Mainframe Forum

Hi All,

Till now i have seen codes where Joins have ON clause associated with them. But herewith i am posting a query where both ON and Where clause are mentioned in a Join.
Please explain me the functioning of the query. Also i have been asked to fine tune the query by my shop. If any one could help me in this regard.

SELECT_021: PROC;

EXEC SQL
SELECT K1.TKKEY ,K1.BOERSE ,K1.WHRG ,
K.VERFALL, K1.GENERATION,
DATE(COALESCE(CHAR(K1.KURSDATUM),'01.01.0001')) AS KDAT1,
DATE(COALESCE(CHAR(K2.KURSDATUM),'01.01.0001')) AS KDAT2,
DATE(COALESCE(CHAR(K3.KURSDATUM),'01.01.0001')) AS KDAT3,
DATE(COALESCE(CHAR(K4.KURSDATUM),'01.01.0001')) AS KDAT4,
DATE(COALESCE(CHAR(K5.KURSDATUM),'01.01.0001')) AS KDAT5,
K1.rowid as rowid1 ,
k2.rowid as rowid2 ,
k3.rowid as rowid3 ,
k4.rowid as rowid4 ,
k5.rowid as rowid5
INTO :VFI021A1P:IVFI021A1
FROM VFI010A1 K
INNER JOIN
VFI020A1 K1 ON K.TKKEY = K1.TKKEY
AND K.BOERSE = K1.BOERSE
AND K.WHRG = K1.WHRG
AND K.VERFALL = K1.VERFALL /* #C02 */
AND K.GENERATION = K1.GENERATION
AND K1.STATUS = 'A'
LEFT OUTER JOIN
VFI020A1 K2 ON K1.TKKEY = K2.TKKEY
AND K1.BOERSE = K2.BOERSE
AND K1.WHRG = K2.WHRG
AND K1.VERFALL = K2.VERFALL /* #C02 */
AND K1.GENERATION = K2.GENERATION
AND K1.KURSDATUM > K2.KURSDATUM
AND K2.STATUS = 'A'
LEFT OUTER JOIN
VFI020A1 K3 ON K1.TKKEY = K3.TKKEY
AND K1.BOERSE = K3.BOERSE
AND K1.WHRG = K3.WHRG
AND K1.VERFALL = K3.VERFALL /* #C02 */
AND K1.GENERATION = K3.GENERATION
AND K1.KURSDATUM > K2.KURSDATUM
AND K2.KURSDATUM > K3.KURSDATUM
AND K3.STATUS = 'A'
LEFT OUTER JOIN
VFI020A1 K4 ON K1.TKKEY = K4.TKKEY
AND K1.BOERSE = K4.BOERSE
AND K1.WHRG = K4.WHRG
AND K1.VERFALL = K4.VERFALL /* #C02 */
AND K1.GENERATION = K4.GENERATION
AND K1.KURSDATUM > K2.KURSDATUM
AND K2.KURSDATUM > K3.KURSDATUM
AND K3.KURSDATUM > K4.KURSDATUM
AND K4.STATUS = 'A'
LEFT OUTER JOIN
VFI020A1 K5 ON K1.TKKEY = K5.TKKEY
AND K1.BOERSE = K5.BOERSE
AND K1.WHRG = K5.WHRG
AND K1.VERFALL = K5.VERFALL /* #C02 */
AND K1.GENERATION = K5.GENERATION
AND K1.KURSDATUM > K2.KURSDATUM
AND K2.KURSDATUM > K3.KURSDATUM
AND K3.KURSDATUM > K4.KURSDATUM
AND K4.KURSDATUM > K5.KURSDATUM
AND K5.STATUS = 'A'
WHERE
K.STATUS = 'A'
AND K1.STATUS = 'A'
AND (K2.STATUS = 'A' OR K2.STATUS is NULL)
AND (K3.STATUS = 'A' OR K3.STATUS is NULL)
AND (K4.STATUS = 'A' OR K4.STATUS is NULL)
AND (K5.STATUS = 'A' OR K5.STATUS is NULL)
AND (K1.KURSDATUM IS NULL OR
K1.KURSDATUM=(SELECT MAX(X.KURSDATUM)
FROM VFI020A1 X
WHERE X.TKKEY = K1.TKKEY
AND X.BOERSE = K1.BOERSE
AND X.WHRG = K1.WHRG
AND X.VERFALL = K1.VERFALL /* #C02 */
AND X.GENERATION = K1.GENERATION
AND X.STATUS = 'A')
)
AND (K2.KURSDATUM IS NULL OR
K2.KURSDATUM=(SELECT MAX(X.KURSDATUM)
FROM VFI020A1 X
WHERE X.TKKEY = K1.TKKEY
AND X.BOERSE = K1.BOERSE
AND X.WHRG = K1.WHRG
AND X.VERFALL = K1.VERFALL /* #C02 */
AND X.GENERATION = K1.GENERATION
AND X.KURSDATUM < K1.KURSDATUM
AND X.STATUS = 'A')
)
AND (K3.KURSDATUM IS NULL OR
K3.KURSDATUM=(SELECT MAX(X.KURSDATUM)
FROM VFI020A1 X
WHERE X.TKKEY = K1.TKKEY
AND X.BOERSE = K1.BOERSE
AND X.WHRG = K1.WHRG
AND X.VERFALL = K1.VERFALL /* #C02 */
AND X.GENERATION = K1.GENERATION
AND X.KURSDATUM < K2.KURSDATUM
AND X.STATUS = 'A')
)
AND (K4.KURSDATUM IS NULL OR
K4.KURSDATUM=(SELECT MAX(X.KURSDATUM)
FROM VFI020A1 X
WHERE X.TKKEY = K1.TKKEY
AND X.BOERSE = K1.BOERSE
AND X.WHRG = K1.WHRG
AND X.VERFALL = K1.VERFALL /* #C02 */
AND X.GENERATION = K1.GENERATION
AND X.KURSDATUM < K3.KURSDATUM
AND X.STATUS = 'A')
)
AND (K5.KURSDATUM IS NULL OR
K5.KURSDATUM=(SELECT MAX(X.KURSDATUM)
FROM VFI020A1 X
WHERE X.TKKEY = K1.TKKEY
AND X.BOERSE = K1.BOERSE
AND X.WHRG = K1.WHRG
AND X.VERFALL = K1.VERFALL /* #C02 */
AND X.GENERATION = K1.GENERATION
AND X.KURSDATUM < K4.KURSDATUM
AND X.STATUS = 'A')
)

AND K.VERFALL = (SELECT MAX(Y.VERFALL)
FROM VFI010A1 Y
WHERE Y.TKKEY = K1.TKKEY
AND Y.BOERSE = K1.BOERSE
AND Y.WHRG = K1.WHRG
AND Y.VERFALL = K1.VERFALL /* #C02 */
AND Y.GENERATION = K1.GENERATION
AND Y.STATUS = 'A')
AND K.TKKEY = :FI0210A
AND K.BOERSE = :FI0210B
AND K.WHRG = :FI0210C
AND K.GENERATION = :FI0210D
AND K.VERFALL = :FI0210E /* #C02 */
FETCH FIRST 1 ROW ONLY
;

END SELECT_021;

It would help if you explained/understood what the query is supposed to return.
If I can read correctly this wil
for a certain (TKKEY,BOERSE, WHRG, GENERATION,VERFALL) on table VFI010A1
it will return the last 5 KURSDATUM & rowid on table VFI020A1 where STATUS = 'A'

Now I'll need to know which Version of DB2 you've got and in what language you are developing. (order by Fetch first in a subselect is only possible in DB 9 NFM)
Does it have to be in one SQLstatement or are you allowed to use a cursor and successive Fetches ? because a cursor , Fetch first 5 record will be a lot faster.
Is (TKKEY,BOERSE, WHRG, GENERATION,VERFALL) a unique index on VFI010A1 ?
Is (TKKEY,BOERSE, WHRG, GENERATION,VERFALL,STATUS,KURSDATUM) a unique index on VFI020A1 ?
Do you think you can handle recursive SQL ? this is also a possibility.

==========================
Also

is kinda useless when you also have
unless you really want to test if the given :FI0210E is indeed the MAX(K.VERFALL)

Hi GuyC,

Thanks for your reply. I will surely get back to you with all your questions.
Yes i am using DB2 9.
All the columns that you have asked for are indexed.
Even i thought of using a Cursor but i need to code it in one SQL statement.
Can you think of any other way to make the query more simpler.

Hello and welcome to the forum,

Even i thought of using a Cursor but i need to code it in one SQL statement.

What business requirement dictates this? This might be someone's preference, but is hard to imagine it is a business requirement.

Code should work correctly all of the time, be easily maintained, and not waste system resources. Getting by with the fewest SQL statements should not be an issue. . .

Hi Dick,

First thanks for replying..
yes its a business req..
just to add few points..
Then columns Guyc asked for are indexed and i am fetching only the first record and not the first 5 records, so in that case i think one SQL statement will do rather than using a cursor..
Infact i am also confused with the query..But i have been asked to make it more simpler and less I/O consuming one.
At present this code is taking too much resources..I get to know this after generating the Strobe report on the transaction.
I would appreciate if you could suggest some thing more simpler.

Hello,

yes its a business req..

Please explain how this is required by the business. . . Someone in Information Technology might want this, but that is not a business requirement. . .

I know of no way to issue a "fetch first" without using a cursor. If there is a way, i'll get to learn a new thing here

Have you run an EXPLAIN of the query? Have you talked with the dba?

Well dick, time to freshen up your db2 knowledge.


following should work in DB2 9 NFM. Unfortenately I'm still 6 weeks away from NFM, so I can't test it myself.

ok : I'll go step by step :
first you'll need to get to the 5 highest kursdatum (in separate rows, but we'll fix that later)
and you'll need to number them from 1to 5 :



once you have this (verify if these are the 5 rows you need),
you'll use that as a subquery to group those 5 rows into 1 row :

oops :
row_number() over order by kursdatum desc) as rn
should be
row_number() over (order by kursdatum desc) as rn

Thanks Guy,

None of the places i've been have installed/upgraded to V9. . .

Maybe by 2012. . .

another typo in the query : I messed up rn and r
since I defined row_number() as rn
I should have used rn in all when clauses of the case

when r=1 then
should be
when rn=1 then

thx rajadasgupta for pointing that out.