zOs/SQL/IAN

select
  UNDERLAYER
 ,titel_n
 ,BOERSE
 ,WHRG
 ,Q_KURS_UMRECHNUNG
 ,KURSDATUM
 ,Q_CS_KA_NUM
 ,cs_ta
 ,sorter
 ,TK_KURSTYP
 ,TK_ZUSATZ
 ,FEHLER
 ,DIFF_PROZ
 ,VERSION
 ,ais_sp
 ,TKKEY
 ,VERFALL
 ,valor_sc
 ,tk_titelart
 ,six_boerse
 ,excode
 ,cs_buart
 ,handlheit
 ,size_uamt
 ,bestand
 ,hboe
 ,isin
 from (
SELECT
  A.UNDERLAYER
 ,VALUE(B.TITEL_N,' ') as titel_n
 ,A.BOERSE
 ,A.WHRG
 ,A.Q_KURS_UMRECHNUNG
 ,A.KURSDATUM
 ,A.Q_CS_KA_NUM
 ,VALUE(B.CS_TA,'0.00') as cs_ta
 ,CASE A.FEHLER
    WHEN 'V303' THEN 'A'
    WHEN 'V304' THEN 'B'
    WHEN 'V362' THEN 'C'
    WHEN 'V302' THEN 'D'
    WHEN 'V311' THEN 'E'
    WHEN 'V313' THEN 'F'
    WHEN 'V314' THEN 'G'
    WHEN 'V347' THEN 'H'
    ELSE 'Z'
  END AS SORTER
 ,A.TK_KURSTYP
 ,A.TK_ZUSATZ
 ,A.FEHLER
 ,DIFF_PROZ
 ,A.VERSION
 ,VALUE(B.AIS_SP,' ') as ais_sp
 ,A.TKKEY
 ,A.VERFALL
 ,VALUE(B.VALOR_SC,0) as valor_sc
 ,VALUE(B.TK_TITELART,' ') as tk_titelart
 ,Value(A.SIX_Boerse,' ') as six_boerse
 ,Value(A.Excode,'00') as excode
 ,VALUE(B.CS_Buart,'0') as cs_buart
 ,VALUE(B.HANDLHEIT,0) as handlheit
 ,VALUE(B.SIZE_UAMT,0) as size_uamt
 ,VALUE(B.BESTAND,'0') as bestand
 ,VALUE(B.HBOE,' ') as hboe
 ,VALUE(C.ISIN,' ') as isin
 FROM               oa1t.VFI060A1 A
 INNER      JOIN    oa1t.VFI010A2 B
 ON   B.TKKEY  = A.TKKEY
 AND  B.BOERSE = A.BOERSE
 AND  B.WHRG   = A.WHRG
 AND  B.BPL_ST = '0'
 AND  B.VERFALL= A.VERFALL
 INNER JOIN         oa1t.VFI011A1 C
 ON   C.TKKEY  = A.TKKEY
 AND  C.VERFALL= A.VERFALL
 WHERE  A.FEHLER IN ('V302','V303','V304','V311'
                    ,'V314','V347','V362','V313')
  AND   A.STATUS   = 'A'
  AND   A.VERFALL  = '31.12.9999'
  AND ((B.VALOR_SC > 4 AND B.Valor_SC < 16) OR VALOR_SC IS NULL)
--AND   A.MUT_TIMESTAMP > (CURRENT TIMESTAMP - 1 MONTH)
  AND   A.MUT_TIMESTAMP > (CURRENT TIMESTAMP - 1 day)
 union all
SELECT DISTINCT
  A.UNDERLAYER
 ,VALUE(B.TITEL_N,' ') as titel_n
 ,A.BOERSE
 ,A.WHRG
 ,A.Q_KURS_UMRECHNUNG
 ,A.KURSDATUM
 ,A.Q_CS_KA_NUM
 ,VALUE(B.CS_TA,'0.00') as cs_ta
 ,CASE A.FEHLER
    WHEN 'V303' THEN 'A'
    WHEN 'V304' THEN 'B'
    WHEN 'V362' THEN 'C'
    WHEN 'V302' THEN 'D'
    WHEN 'V311' THEN 'E'
    WHEN 'V313' THEN 'F'
    WHEN 'V314' THEN 'G'
    WHEN 'V347' THEN 'H'
    ELSE 'Z'
  END AS SORTER
 ,A.TK_KURSTYP
 ,A.TK_ZUSATZ
 ,A.FEHLER
 ,DIFF_PROZ
 ,A.VERSION
 ,VALUE(B.AIS_SP,' ') as ais_sp
 ,A.TKKEY
 ,A.VERFALL
 ,VALUE(B.VALOR_SC,0) as valor_sc
 ,VALUE(B.TK_TITELART,' ') as tk_titelart
 ,Value(A.SIX_Boerse,' ') as six_boerse
 ,Value(A.Excode,'00') as excode
 ,VALUE(B.CS_Buart,'0') as cs_buart
 ,VALUE(B.HANDLHEIT,0) as handlheit
 ,VALUE(B.SIZE_UAMT,0) as size_uamt
 ,VALUE(B.BESTAND,'0') as bestand
 ,VALUE(B.HBOE,' ') as hboe
 ,VALUE(C.ISIN,' ') as isin
 FROM               oa1t.VFI060A1 A
 INNER      JOIN    oa1t.VFI010A2 B
 ON   B.TKKEY  = A.TKKEY
 AND  (B.BOERSE ^= A.BOERSE
       or B.WHRG ^= A.WHRG)
 AND (A.FEHLER = 'V362' OR B.BPL_ST = '0')
 AND  B.VERFALL= A.VERFALL
 --AND  B.BPL_ST = '0'
 INNER JOIN         oa1t.VFI011A1 C
 ON   C.TKKEY  = A.TKKEY
 AND  C.VERFALL= A.VERFALL
 WHERE  A.FEHLER IN ('V302','V303','V304','V311'
                    ,'V314','V347','V362','V313')
  AND   A.STATUS   = 'A'
  AND   A.VERFALL  = '31.12.9999'
  AND ((B.VALOR_SC > 4 AND B.Valor_SC < 16) OR VALOR_SC IS NULL)
--AND   A.MUT_TIMESTAMP > (CURRENT TIMESTAMP - 1 MONTH)
  AND   A.MUT_TIMESTAMP > (CURRENT TIMESTAMP - 1 day)
 ) x
  ORDER BY bestand      DESC
          ,SORTER       ASC
          ,UNDERLAYER   ASC
          ,BOERSE       ASC
          ,WHRG         ASC
          ,KURSDATUM    ASC
;x;
//
//A273818T JOB (SB00,KE20,,TT00000000),'BATCH SQL',
//        NOTIFY=&SYSUID,REGION=5M,
//        MSGCLASS=A,TIME=MAXIMUM
//* -------------------
//SQL     EXEC PGM=IKJEFT01,
//* ----------------------------------------------------------
//             DYNAMNBR=30
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD *
 DSN SYSTEM(DBTF)
 RUN  PROGRAM(DSNTEP2)
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *