zOs/SQL/REODDL2

set current sqlid = 'S100447';
---------------------------------------------------------------------
-- test tReoRunJob (and tReoRunPart.reoTst)
-- message e not null ==> error message ==> see case statement below
-- eo2 = calculate better eoj
---------------------------------------------------------------------
create view s100447.vReoRunJobChk as
with p as -- part gruppiert nach job, nur ty='r'
(select tst, ty, count(*) cnt,
     count(reotst) cntReo,
     min(reoTst) reoTstVon,
     max(reoTst) reoTstBis
    from s100447.tReoRunPart
    where sta = 'r'
    group by tst, ty
), j as -- join to job
(
select j.*,
       p.ty pTy, p.cnt pCnt, p.cntReo,
       p.reoTstVon, p.reoTstBis,
       (select min(tst)
            from s100447.tReoRunJob a
            where a.job = j.job and a.tst > j.tst
       ) nextJob
    from s100447.tReoRunJob j
      left join p
        on j.tst = p.tst and p.ty = lower(left(j.ty, 1))
)
        ----> diese case statement macht error message <-------------
select  case when eoj is null and nextJob is not null
                 then 'null not last'
             when reoTstVon < tst then 'reoTst < tst'
             when reoTstBis > nextJob then 'reoTst > nextJob'
             when reoTstBis > eoj then 'reoTst > eoj'
             when eoj > nextJob then 'overlap nextJob'
             when reoTstBis - tst > 1000000 then 'reoTst > 1 day'
      --     when eoj - tst > 1000000 then 'eoj > 1 day'
             else null
        end e,
        case when pCnt is null or pCnt < 1 then tst
             when reoTstBis is not null
                  and (nextJob is null or nextJob > reoTstBis)
                  and (tst < current timestamp - 2 days
                        or pCnt = cntReo) then reoTstBis
             when nextJob is not null then
                  nextJob
             else null
        end eo2, j.*
    from j
;
---------------------------------------------------------------------
-- check tReoRunPart
-- message e not null ==> error message ==> see case statement below
-- newTime = calculate reoTime from current values
---------------------------------------------------------------------
create view S100447.vReoRunRngChk as
with r as -- part gruppiert nach range
(
select
    (select j.job from s100447.tReoRunJob j where j.tst = p.tst) job,
    p.tst, p.rng,
    min(ty) ty,
    count(distinct ty) cdTy,
    min(sta) sta,
    count(distinct sta) cdSta,
    sum(case when part = paVon then 1 else 0 end) cPaVon,
    sum(case when part = paBis then 1 else 0 end) cPaBis,
    count(*) cnt,
    count(distinct part) cdPart,
    count(reoTst) cReoTst,
    min(reoTst) minReoTst,
    max(reoTst) maxReoTst,
    (select max(b.reoTst) -- letzter reoTst aus vorgehenden Ranges
          from s100447.tReoRunPart b
          where b.tst = p.tst and b.rng < p.rng) befReoTst,
    (select min(sta) || max(sta) -- status und reoTst aus Range -1
            || case when count(*) = count(reotst) then '=' else '0' end
            || coalesce(char(max(reoTst)), '-')
          from s100447.tReoRunPart b
          where b.tst = p.tst and b.rng = p.rng-1) r1ReoTst,
    (select min(sta) || max(sta) -- status und reoTst aus Range -2
            || case when count(*) = count(reotst) then '=' else '0' end
            || coalesce(char(max(reoTst)), '-')
          from s100447.tReoRunPart b
          where b.tst = p.tst and b.rng = p.rng-2) r2ReoTst,
    max(case when part = paVon then reoTime else null end) reoTime
  from s100447.tReoRunPart p
  group by tst, rng
) , s as -- begTst aus range -1 und range -2 berechnen
( select r.*,
      case when rng = 1 then tst
           when left(r1ReoTst, 3) = 'rr='
               then timestamp(substr(r1ReoTst, 4))
           when left(r1ReoTst, 2) <> '00' then null
           when rng = 2 then tst
           when left(r2ReoTst, 3) = 'rr='
               then timestamp(substr(r2ReoTst, 4))
           else null
           end begTst
    from r
), t as -- aktuelle reoZeit berechnen
( select s.*,
        (days(maxReoTst) - days(begTst)) * 86400
        + midnight_seconds(maxReoTst) - midnight_seconds(begTst)
        + 1e-6 * (microsecond(maxReoTst)-microsecond(begTst)) newTime
    from s
)
select ---> case statement macht error message <--------------------
    case when job is null   then 'job missing'
         when cdTy <> 1     then 'ty not unique'
         when cdSta <> 1    then 'sta not unique'
         when cPaVon <> 1   then 'part=paVon not unique'
         when cPaBis <> 1   then 'part=paBis not unique'
         when cdPart <> cnt then 'part not distinct'
         when minReoTst < tst then 'reoTst < tst'
                           -- rng waren früher anders sortiert
   --    when tst < '2011-09-29-00.00.00'  then null
         when befReoTst > minReoTst then 'before ReoTst > minReoTst'
         when abs(reoTime-newTime) >= 1 then 'reoTime <> newTime'
         else null
    end e,
    t.*
    from t
;