zOs/SQL/BIPA9LT3

with n as
( select APPL
       , PGM
       , COLLID
       , INSTALLDATE
,case when OWNER        <> '' then owner        else null end owner
,case when QUALIFIER    <> '' then QUALIFIER    else null end QUALIFIER
,case when ISOLATION    <> '' then ISOLATION    else null end ISOLATION
,case when EXPLAIN      <> '' then EXPLAIN      else null end EXPLAIN
,case when CURRENTDATA <> '' then CURRENTDATA else null end CURRENTDATA
,case when RELEASE      <> '' then RELEASE      else null end RELEASE
,case when DBPROTOCOL  <> '' then DBPROTOCOL  else null end DBPROTOCOL
,case when DEGREE       <> '' then DEGREE       else null end DEGREE
,case when DYNAMICRULES <> '' then DYNAMICRULES else null end
              DYNAMICRULES
,case when KEEPDYNAMIC <> '' then KEEPDYNAMIC else null end KEEPDYNAMIC
,case when REOPT        <> '' then REOPT        else null end REOPT
,case when OPTHINT      <> '' then OPTHINT      else null end OPTHINT
,case when IMMEDWRITE   <> '' then IMMEDWRITE   else null end IMMEDWRITE
,case when FLAG         <> '' then FLAG         else null end FLAG
,case when PATH         <> '' then PATH         else null end PATH
,case when VALIDATE     <> '' then VALIDATE     else null end VALIDATE
,case when SQLERROR     <> '' then SQLERROR     else null end SQLERROR
,case when SQLRULES     <> '' then SQLRULES     else null end SQLRULES
,case when DEFER        <> '' then DEFER        else null end DEFER
,case when ENCODING     <> '' then ENCODING     else null end ENCODING
,case when ROUNDING     <> '' then ROUNDING     else null end ROUNDING
    from oa1p.bind_parameters
)
, d as
( select
       case when s.pgm <> 'DEFAULT' then 's' else ' ' end
    || case when a.appl <> 'DEFAULT' then 'a' else ' ' end
    || case when d.appl is null then ' ' else 'd' end sad
--alue(s.?           ,a.?           ,d.?           ) ?
,value(s.APPL        ,a.APPL        ,d.APPL        ) APPL
,value(s.PGM         ,a.PGM         ,d.PGM         ) PGM
,value(s.COLLID      ,a.COLLID      ,d.COLLID      ) COLLID
,value(s.INSTALLDATE ,a.INSTALLDATE ,d.INSTALLDATE ) INSTALLDATE
,value(s.OWNER       ,a.OWNER       ,d.OWNER       ) OWNER
,value(s.QUALIFIER   ,a.QUALIFIER   ,d.QUALIFIER   ) QUALIFIER
,value(s.ISOLATION   ,a.ISOLATION   ,d.ISOLATION   ) ISOLATION
,value(s.EXPLAIN     ,a.EXPLAIN     ,d.EXPLAIN     ) EXPLAIN
,value(s.CURRENTDATA ,a.CURRENTDATA ,d.CURRENTDATA ) CURRENTDATA
,value(s.RELEASE     ,a.RELEASE     ,d.RELEASE     ) RELEASE
,value(s.DBPROTOCOL  ,a.DBPROTOCOL  ,d.DBPROTOCOL  ) DBPROTOCOL
,value(s.DEGREE      ,a.DEGREE      ,d.DEGREE      ) DEGREE
,value(s.DYNAMICRULES,a.DYNAMICRULES,d.DYNAMICRULES) DYNAMICRULES
,value(s.KEEPDYNAMIC ,a.KEEPDYNAMIC ,d.KEEPDYNAMIC ) KEEPDYNAMIC
,value(s.REOPT       ,a.REOPT       ,d.REOPT       ) REOPT
,value(s.OPTHINT     ,a.OPTHINT     ,d.OPTHINT     ) OPTHINT
,value(s.IMMEDWRITE  ,a.IMMEDWRITE  ,d.IMMEDWRITE  ) IMMEDWRITE
,value(s.FLAG        ,a.FLAG        ,d.FLAG        ) FLAG
,value(s.PATH        ,a.PATH        ,d.PATH        ) PATH
,value(s.VALIDATE    ,a.VALIDATE    ,d.VALIDATE    ) VALIDATE
,value(s.SQLERROR    ,a.SQLERROR    ,d.SQLERROR    ) SQLERROR
,value(s.SQLRULES    ,a.SQLRULES    ,d.SQLRULES    ) SQLRULES
,value(s.DEFER       ,a.DEFER       ,d.DEFER       ) DEFER
,value(s.ENCODING    ,a.ENCODING    ,d.ENCODING    ) ENCODING
,value(s.ROUNDING    ,a.ROUNDING    ,d.ROUNDING    ) ROUNDING
    from n s
      left join n a
        on a.appl = s.appl and a.pgm = 'DEFAULT'
        and a.collid = 'DEFAULT'
      left join oa1p.bind_parameters d
        on d.appl = 'DEFAULT' and d.pgm = 'DEFAULT'
        and d.collid = 'DEFAULT'
)
, e as
(
  select
     left(release, 4) || left(degree, 4) || left(dynamicRules, 4)
       || left(reopt, 4) || left(opthint, 4) || left(validate, 4) comb,
     d.*
     from d
)
, f as
( select   case
           when comb = '                        ' then '         '
           when comb = '    ANY BIND        BIND' then 'degAny   '
           when comb = '    1   BIND        BIND' then 'default  '
           when comb = '    1   BIND        RUN ' then 'valRun   '
           when comb = '    1   BIND    HBD1BIND' then 'optHBD1  '
           when comb = '    1   BIND    OH  BIND' then 'optOH    '
           when comb = '    1   BINDALWA    BIND' then 'reoAlw   '
           when comb = '    1   RUN         BIND' then 'dynRun   '
           when comb = '    1   RUN         RUN ' then 'dynValRun'
           when comb = 'DEAL1   BIND        BIND' then 'RelDea   '
           else '???' end neu,
           e.*
     from e
)
select count(*), min(sad), max(sad)
      , comb "RelaDegrDynaReopOpthVali"
      , neu
    from f
    where appl not like 'A18Q%'
    group by comb, neu
    order by comb
;x;
select count(*), min(sad), max(sad)
      , min(comb) "RelaDegrDynaReopOpthVali"
      , max(comb) "RelaDegrDynaReopOpthVali"
               , OWNER
           --  , QUALIFIER
               , ISOLATION
               , EXPLAIN
               , CURRENTDATA
               , RELEASE
               , DBPROTOCOL
               , DEGREE
               , DYNAMICRULES
               , KEEPDYNAMIC
               , REOPT
               , OPTHINT
               , IMMEDWRITE
               , FLAG
               , PATH
               , VALIDATE
               , SQLERROR
               , SQLRULES
               , DEFER
               , ENCODING
               , ROUNDING
    from e
    where appl not like 'A18Q%'
    group by    OWNER
         --   , QUALIFIER
              , ISOLATION
              , EXPLAIN
              , CURRENTDATA
              , RELEASE
              , DBPROTOCOL
              , DEGREE
              , DYNAMICRULES
              , KEEPDYNAMIC
              , REOPT
              , OPTHINT
              , IMMEDWRITE
              , FLAG
              , PATH
              , VALIDATE
              , SQLERROR
              , SQLRULES
              , DEFER
              , ENCODING
              , ROUNDING
    order by    OWNER
        --    , QUALIFIER
              , ISOLATION
              , EXPLAIN
              , CURRENTDATA
              , RELEASE
              , DBPROTOCOL
              , DEGREE
              , DYNAMICRULES
              , KEEPDYNAMIC
              , REOPT
              , OPTHINT
              , IMMEDWRITE
              , FLAG
              , PATH
              , VALIDATE
              , SQLERROR
              , SQLRULES
              , DEFER
              , ENCODING
              , ROUNDING