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;