SELECT UNDERLAYER , 00010000
LAUFNR , 00020000
VERFALL , 00030000
CASE WHEN BASISPREIS >= 10000000 THEN 9999999.999999 00040000
WHEN BASISPREIS <= -10000000 THEN -9999999.999999 00050000
ELSE DECIMAL(BASISPREIS,13,6) 00060000
END , 00070000
CASE WHEN KONTRAKT >= 10000000 THEN 9999999.999999 00080000
WHEN KONTRAKT <= -10000000 THEN -9999999.999999 00090000
ELSE DECIMAL(KONTRAKT ,13,6) 00100000
END , 00110000
VERSION , 00120000
MUT_TIMESTAMP, 00130000
TKKEY, YEAR(VERFALL), MONTH(VERFALL) 00140000
FROM OA1A.VFI011A1 A 00150000
WHERE A.UNDERLAYER = 30000000 00160000
AND A.LAUFNR = 2 00170000
AND A.VERFALL >= '01.01.1999' 00180000
AND A.MUT_TIMESTAMP = (SELECT MAX(MUT_TIMESTAMP) 00190000
FROM OA1A.VFI011A1 B 00200001
WHERE B.UNDERLAYER = A.UNDERLAYER 00210001
AND B.LAUFNR = A.LAUFNR 00220001
AND B.VERFALL = A.VERFALL 00230001
AND B.BASISPREIS = A.BASISPREIS 00240001
AND B.KONTRAKT = A.KONTRAKT 00250001
AND B.VERSION = A.VERSION) 00260001
ORDER BY 1, 2, 9, 10, 4, 5, 3, 6 00270000
; 00280000
XREATE VIEW OA1A.VFI011A1 00290001
( UNDERLAYER, LAUFNR, VERFALL, BASISPREIS, KONTRAKT, 00300001
VERSION, MUT_TIMESTAMP, TKKEY) AS 00310001
SELECT FI01101A, FI01102A, FI01103A, FI01104A, FI01105A, 00320001
FI01106A, FI01107A, FI01108A 00330001
FROM OA1A.TFI011A1 ; 00340001
00350000