zOs/SQL/BIPA9LT4

insert into oa1p.tbiParm (type, prio, appl, pkg, rz, ssid, location,
           value, installfr)
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 = '    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 'opt' type
     , case when pgm <> 'DEFAULT' then '20'
            else '10' end prio
     , left(case when appl = 'DEFAULT' then '*' else appl end, 4) appl
     , case when pgm  = 'DEFAULT' then '*' else pgm  end pkg
     , '*' rz
     , '*' ssid
     , '*' location
     , neu value
     , installDate installFr
 --  , f.*
     from f
     where appl not like 'A18Q%'
       and neu <> 'default'
;
commit;