zOs/SQL/BIPA9LT6

with a as
(
select case when left(pgm, 2) = 'YX' then 'YX'
            when left(pgm, 2) = 'YY' then 'YY'
            when left(pgm, 1) = 'Y'  then substr(pgm, 2, 2)
            else left(pgm, 2) end q1,
        p.*
  from (
     select           appl, pgm, collid from oa1p.bind_parameters
     union all select appl, pgm, collid from oa1p.bind_exceptions
     ) p
  where appl <> 'A18Q' and appl <> 'B18Q' and pgm <> 'DEFAULT'
), b as
(
select appl, pgm, count(*) cnt,
    sum(case when q1 <> left(collid, 2) then 1 else 0 end) diff,
    count(distinct left(collid, 2)) dCol,
    q1, min(collid) colFr, max(collid) colTo
    from a
    group by q1, appl, pgm
    having  0 <> sum(case when q1 <> left(collid, 2) then 1 else 0 end)
)
, n as
(
select case
            when cnt = 2 and colFr = 'OA1P01'
                   and  colTo ='OA1P02' then 'OA1P-02'
            when dCol = 1 and colFr = 'AV01' and cnt = 2 then 'AV-02'
            when dCol = 1 and colFr = 'AV01' and cnt > 27 then 'AVALL'
            when dCol = 1 and colFr = 'XC00' and cnt = 10 then 'XCALL'
            when dCol = 1 then left(colFr, 2)
            when dCol = 2 and (('YY' = colFr and  left(colTo, 2) = q1)
                           or  ('YY' = colTo and  left(colFr, 2) = q1))
                           then 'YY+'
            when dCol = 2 and 'CT' = colFr and  left(colTo, 2) = 'CZ'
                          and q1 = 'CT' then 'CZ+'
            else '???'
       end new,
       b.*
    from b
)
select * from n order by new, 7, 8
;x;
select new, count(*)
    from n
    group by new
;x;

select *
  from            oa1p.bind_exceptions p
  where pgm = 'YXAUGOD'
;x;
;;;
select q1, left(collid, 2) c2, appl, pgm, count(*)
    from a
    where q1 <> left(collid, 2)
    group by q1, left(collid, 2) , appl, pgm
;;;
select q1, left(collid, 2) c2, appl, pgm, count(*)
    from a
    where q1 <> left(collid, 2)
    group by q1, left(collid, 2) , appl, pgm
;;;
select *
  from            oa1p.bind_exceptions p
  where length(strip(collid)) <> 2
;x;
--where qualifier  <> 'BUA' and qualifier not like 'OA1$%'
  where pgm = 'YPCEVPO'
  ;x;
select qualifier,  count(*)
  from            oa1p.bind_parameters p
  group by qualifier
  ;x;
select *
  from            oa1p.bind_exceptions      e
  where e.pgm = 'YAU019U'
  ;
select
  e.ssid,e.location,e.collid,e.qualifier,
  p.appl,p.pgm,p.collid,p.installdate
  from            oa1p.bind_parameters p
  left outer join oa1p.bind_exceptions e
  on  e.appl   = p.appl
  and e.pgm    = p.pgm
  and e.collid = p.collid
  and e.installdate  = p.installdate
  where p.pgm = 'YAU019U'
    and (e.ssid= 'DBAF' or e.ssid is NULL)
;
select
  e.ssid,e.location,e.collid,e.qualifier,
  p.appl,p.pgm,p.collid,p.installdate
  from            oa1p.bind_parameters p
  left outer join oa1p.bind_exceptions e
  on  e.appl   = p.appl
  and e.pgm    = p.pgm
  and e.collid = p.collid
  and e.installdate  = p.installdate
    and  e.ssid= 'DBAF'
  where p.pgm = 'YAU019U'