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.
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')
is kinda useless when you also have
AND K.VERFALL = :FI0210E /* #C02 */
unless you really want to test if the given :FI0210E is indeed the MAX(K.VERFALL)
I can explain it to you, but i can not understand it for you.