zOs/SQL/ABUBDDX

-- create view oa1p.vQZ045AbubState as
with r as
(
  select cast( case when tiOuTy = 'e' then evTst else start end
                + tiOuSecs seconds as timestamp(0)) until
      , r.*, c.*
    from oa1p.vQZ045AbUbLast r
      left join oa1p.vQZ046AbUbCur c
         on r.type = 'ab' and r.subType = c.cal
)
, s as
(
  select
        case when event is null or evTst is null then 'timeout'
             when cal is null   then event
             when evTst >= start and event in ('ok', 'err') then event
             when evTst >= start and until > current timestamp then '>'
             when (evTst >= prStart or tiOuTy = 'e')
                  and until > current timestamp
                  and event in ('ok', 'err') then event
             when conEv = 'err' then 'conTimeout'
             when conTst + tiOuSecs seconds > current timestamp
                                               then 'conRestart'
             else 'timeout'
        end state
      , r.*
    from r
)
, o as
(
  select rz, dbSy, ab
      , char(case when type = 'conn' and conTst > abubTst then 'a'
             when type = 'conn' or state in ('ok', '>')   then ''
             when state in ('conTimeout', 'conRestart')   then 'c'
             when state = 'timeout' and (until <= abubTst
                 or until is null or abubTst is null)     then ''
             when state <> 'timeout' and (evTst <= abubTst
                 or evTst is null or abubTst is null)     then ''
                                                          else 'a'
        end, 1) alarm
      , char(state, 10) state
      , event, evTst, orTst, link, cont
      , start, until, cal, tiOuTy, tiOuSecs, calVa4, prStart
      , conEv, conTst
      , abubTst
      , type, subType, va3, va4
    from s
    order by conPri, dbSy, ab
)
select * from o
;
commit
;
select * from  oa1p.vQZ045AbubState ;
sele