zOs/SQL/HAEUPT

select a3.wk001name, a3.wk001num, a1.wk001name, a1.wk001num
    from A540769.TWK001A a1,
         A540769.TWK001A a2,
         A540769.TWK001A a3
    where a2.wk001num =
        ( select max(q.wk001num)
            from A540769.TWK001A q
            where q.wk001num < a1.wk001num
        )
      and a3.wk001num =
        ( select max(q.wk001num)
            from A540769.TWK001A q
            where q.wk001num < a2.wk001num
        )
;
delete
  from A540769.TWK001A a3
  where exists
      ( select *
          from A540769.TWK001A a1,
               A540769.TWK001A a2
          where a2.wk001num =
              ( select max(q.wk001num)
                  from A540769.TWK001A q
                  where q.wk001num < a1.wk001num
              )
            and a3.wk001num =
              ( select max(q.wk001num)
                  from A540769.TWK001A q
                  where q.wk001num < a2.wk001num
              )
      )
;
rollback
;
select a1.wk001name, a1.wk001num
    from A540769.TWK001A a1
    where 3 <=
        ( select count(distinct q.wk001num)
            from A540769.TWK001A q
            where q.wk001num > a1.wk001num
        )
;
xx
;
insert into gdb0351.vfi020h1
select distinct
 k.tkkey ,k.boerse ,k.whrg ,k.kursdatum ,k.tk_kurstyp
,k.tk_zusatz ,k.generation ,k.mut_timestamp ,k.cs_ka_num
,k.sett_code ,k.kurs_lieferung ,k.kurs_umrechnung ,k.whr_umr
,k.menge ,k.boerse_tk ,k.erstellung ,k.flag_p ,k.status
,k.gueltig_von ,k.quelle ,k.log_eintrag ,k.instrad ,k.mut_stat
,k.ks_bpl_st ,k.ks_bpl_ht ,k.ks_bpl_ks ,k.ks_alt_ks ,k.ks_bpl_ha
,k.ks_ais_sp ,k.ks_bpl_in ,k.ks_bpl_sp
from gdb0351.vfi020a1 k,
(select k1.tkkey ,k1.boerse ,k1.whrg ,k5.kursdatum
 from gdb0351.vfi020a1 k1,gdb0351.vfi020a1 k2,gdb0351.vfi020a1 k3,
  gdb0351.vfi020a1 k4,gdb0351.vfi020a1 k5
  where k1.tkkey=k2.tkkey
  and k1.tkkey=k3.tkkey
  and k1.tkkey=k4.tkkey
  and k1.tkkey=k5.tkkey
  and k1.boerse=k2.boerse
  and k1.boerse=k3.boerse
  and k1.boerse=k4.boerse
  and k1.boerse=k5.boerse
  and k1.whrg=k2.whrg
  and k1.whrg=k3.whrg
  and k1.whrg=k4.whrg
  and k1.whrg=k5.whrg
  and k1.kursdatum=(select max(kursdatum) from gdb0351.vfi020a1
   where tkkey=k1.tkkey
   and boerse=k1.boerse
   and whrg=k1.whrg )
  and k2.kursdatum=(select max(kursdatum) from gdb0351.vfi020a1
   where tkkey=k1.tkkey
   and boerse=k1.boerse
   and whrg=k1.whrg
   and kursdatum<k1.kursdatum )
  and k3.kursdatum=(select max(kursdatum) from gdb0351.vfi020a1
   where tkkey=k1.tkkey
   and boerse=k1.boerse
   and whrg=k1.whrg
   and kursdatum<k2.kursdatum )
  and k4.kursdatum=(select max(kursdatum) from gdb0351.vfi020a1
   where tkkey=k1.tkkey
   and boerse=k1.boerse
   and whrg=k1.whrg
   and kursdatum<k3.kursdatum )
  and k5.kursdatum=(select max(kursdatum) from gdb0351.vfi020a1
   where tkkey=k1.tkkey
   and boerse=k1.boerse
   and whrg=k1.whrg
   and kursdatum<k4.kursdatum )              gdb0351.vfi020a1
--and k1.tkkey=1213853
--and k1.boerse='000000000380'
--and k1.whrg='0010'
  ) k0
 where k.tkkey=k0.tkkey
 and k.boerse=k0.boerse
 and k.whrg=k0.whrg
 and k.kursdatum<k0.kursdatum
--fetch first 1000 rows only
--with ur
----------------------
xelete from gdb0351.vfi020a1 k where exists
(select k1.tkkey ,k1.boerse ,k1.whrg ,k5.kursdatum
 from gdb0351.vfi020a1 k1,gdb0351.vfi020a1 k2,gdb0351.vfi020a1 k3,
  gdb0351.vfi020a1 k4,gdb0351.vfi020a1 k5
  where k1.tkkey=k2.tkkey
  and k1.tkkey=k3.tkkey
  and k1.tkkey=k4.tkkey
  and k1.tkkey=k5.tkkey
  and k1.boerse=k2.boerse
  and k1.boerse=k3.boerse
  and k1.boerse=k4.boerse
  and k1.boerse=k5.boerse
  and k1.whrg=k2.whrg
  and k1.whrg=k3.whrg
  and k1.whrg=k4.whrg
  and k1.whrg=k5.whrg
  and k1.kursdatum=(select max(kursdatum) from gdb0351.vfi020a1
   where tkkey=k1.tkkey
   and boerse=k1.boerse
   and whrg=k1.whrg )
  and k2.kursdatum=(select max(kursdatum) from gdb0351.vfi020a1
   where tkkey=k1.tkkey
   and boerse=k1.boerse
   and whrg=k1.whrg
   and kursdatum<k1.kursdatum )
  and k3.kursdatum=(select max(kursdatum) from gdb0351.vfi020a1
   where tkkey=k1.tkkey
   and boerse=k1.boerse
   and whrg=k1.whrg
   and kursdatum<k3.kursdatum )
  and k5.kursdatum=(select max(kursdatum) from gdb0351.vfi020a1
   where tkkey=k1.tkkey
   and boerse=k1.boerse
   and whrg=k1.whrg
   and kursdatum<k4.kursdatum )
  and k1.tkkey=1213853
  and k1.boerse='000000000380'
  and k1.whrg='0010'
  ) -- k0
 -- and  k.tkkey=k0.tkkey
 ---and k.boerse=k0.boerse
 --  an d k.whrg=k0.whrg
 -- and k.kursdatum<k0.kursdatum
 ;
 rollback
 ;