zOs/SQL/ABUBDD1

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
)
, s1 as
(
  select           -- s1 = timout or alarmTimeout (new)
        case when event is null or evTst is null then ' t'
             when cal is null                    then ''
             when evTst >= start and event not like '>%' then ''
             when until <  abubTst               then ' t'
             when until <  current timestamp     then 'at'
             when evTst >= start or tiOuTy = 'e' then ''
             when evTst < prStart or event like '>%' then ' t'
                                                 else ''
        end s1
        , r.*
        from r
)
, s2 as
(
  select
        case
          when type = 'conn' then  -- connection: alarm?
            case when conTst is null    then ' timeout'
                 when conTst >= abubTst then 'a' || event
                                        else ' ' || event
            end
          when s1 = '' then        -- event: in progress or alarm?
            case when event like '>%' then ' >'
                 when event = 'ok' or evTst <= abubTst then ' '||event
                                                       else 'a'||event
            end
          else                     -- timeout: is connection the reason?
            case when conEv is null or conEv <> 'ok' then 'cconTimeout'
                 when conTst + tiOuSecs seconds > current timestamp
                                               then 'cconRestart'
                 else left(s1, 1) || 'timeout'
            end
        end s2
      , s1.*
    from s1
)
, o as
(
  select rz, dbSy, ab
      , substr(s2, 1, 1) alarm
      , substr(s2, 2, 10) state
      , s1
      , event, evTst, orTst, link, cont
      , start, until, cal, tiOuTy, tiOuSecs, calVa4, prStart
      , conEv, conTst
      , abubTst
      , type, subType, va3, va4
    from s2
    order by conPri, dbSy, ab
)
select o.rz, o.dbSy, o.ab, o.alarm, v.alarm, o.state, v.state, o.s1
      , o.*, v.*
    from o full outer join  oa1p.vQZ045AbubState v
        on o.rz = v.rz and o.dbSy = v.dbSy and o.ab = v.ab
;
commit