EXPLAIN PLAN SET QUERYNO=231 FOR 00010009
SELECT UNDERLAYER , 00020009
LAUFNR , 00030009
VERFALL , 00040009
CASE WHEN BASISPREIS >= 10000000 THEN 9999999.999999 00050009
WHEN BASISPREIS <= -10000000 THEN -9999999.999999 00060009
ELSE DECIMAL(BASISPREIS,13,6) 00070009
END , 00080009
CASE WHEN KONTRAKT >= 10000000 THEN 9999999.999999 00090009
WHEN KONTRAKT <= -10000000 THEN -9999999.999999 00100009
ELSE DECIMAL(KONTRAKT ,13,6) 00110009
END , 00120009
VERSION , 00130009
MUT_TIMESTAMP, 00140009
TKKEY, YEAR(VERFALL), MONTH(VERFALL) 00150009
FROM Oa1t.VFI011A1 A 00160021
WHERE A.UNDERLAYER = 30000000 00170023
AND A.LAUFNR = 2 00180013
AND A.VERFALL >= '01.01.1999' 00190009
AND A.MUT_TIMESTAMP = (SELECT MAX(MUT_TIMESTAMP) 00200009
FROM Oa1t.VFI011A1 B 00210021
WHERE B.UNDERLAYER = A.UNDERLAYER 00220009
AND B.LAUFNR = A.LAUFNR 00230009
AND B.VERFALL = A.VERFALL 00240009
AND B.BASISPREIS = A.BASISPREIS 00250009
AND B.KONTRAKT = A.KONTRAKT 00260009
AND B.VERSION = A.VERSION) 00270009
ORDER BY 1, 2, 9, 10, 4, 5, 3, 6 00280009
; 00290009
select * 00300014
from A540769.plan_view5 v 00310024
where -- progname = 'DSNREXX' 00320014
queryno =231 -- between 1000000 and 1009999 00330014
order by applname, progname, queryNo, qblockno, planno 00340014
; 00350014
COMMIT 00360009
; 00370009
XREATE VIEW Oa1t.VFI011A1 00380021
( UNDERLAYER, LAUFNR, VERFALL, BASISPREIS, KONTRAKT, 00390009
VERSION, MUT_TIMESTAMP, TKKEY) AS 00400009
SELECT FI01101A, FI01102A, FI01103A, FI01104A, FI01105A, 00410009
FI01106A, FI01107A, FI01108A 00420009
FROM Oa1t.TFI011A1 ; 00430021
00440009
EXPLAIN PLAN SET QUERYNO=231 FOR 00450002
SELECT A.UNDERLAYER , 00460005
A.LAUFNR , 00470005
A.VERFALL , 00480005
CASE WHEN A.BASISPREIS >= 10000000 THEN 9999999.999999 00490005
WHEN A.BASISPREIS <= -10000000 THEN -9999999.999999 00500005
ELSE DECIMAL(A.BASISPREIS,13,6) 00510005
END , 00520000
CASE WHEN A.KONTRAKT >= 10000000 THEN 9999999.999999 00530005
WHEN A.KONTRAKT <= -10000000 THEN -9999999.999999 00540005
ELSE DECIMAL(A.KONTRAKT ,13,6) 00550008
END , 00560000
A.VERSION , 00570005
A.MUT_TIMESTAMP, 00580005
A.TKKEY, YEAR(A.VERFALL), MONTH(A.VERFALL) 00590005
FROM Oa1t.VFI011A1 A 00600021
JOIN 00610004
( SELECT UNDERLAYER, 00620004
LAUFNR, 00630004
VERFALL, 00640004
MAX(MUT_TIMESTAMP) MUT_TIMESTAMP, 00650006
BASISPREIS, 00660004
KONTRAKT, 00670004
VERSION 00680007
FROM Oa1t.VFI011A1 00690021
GROUP BY UNDERLAYER, 00700004
LAUFNR, 00710004
VERFALL, 00720004
BASISPREIS, 00730004
KONTRAKT, 00740004
VERSION 00750004
) B 00760004
ON 00770004
B.UNDERLAYER = A.UNDERLAYER 00780004
AND B.LAUFNR = A.LAUFNR 00790004
AND B.VERFALL = A.VERFALL 00800004
AND B.MUT_TIMESTAMP = A.MUT_TIMESTAMP 00810004
AND B.BASISPREIS = A.BASISPREIS 00820004
AND B.KONTRAKT = A.KONTRAKT 00830004
AND B.VERSION = A.VERSION 00840004
WHERE A.UNDERLAYER = 30000000 00850000
AND A.LAUFNR = 2 00860000
AND A.VERFALL >= '01.01.1999' 00870000
ORDER BY 1, 2, 9, 10, 4, 5, 3, 6 00880000
; 00890000
00900000