zOs/SQL/BIPA9LT5

with b as (
select appl a, pgm p, qualifier q, cF, cT,
       row_number() over(partition by appl, pgm
                         order by appl, pgm, qualifier) r
  from (select    appl, pgm, qualifier, min(collid) cF, max(collid) cT
         from oa1p.bind_parameters
         group by appl, pgm, qualifier ) q
)
, r (a, p, q1,  q, r, cF, cT) as
(
select a, p, q, varchar(strip(q), 500), 1, cF, cT
  from b where r = 1
union all select r.a, r.p, b.q,
            r.q || ' ' || strip(b.q), r.r+1,
            min(r.cF, b.cF), max(r.cT, b.cT)
     from r, b
     where r.a  = b.a and r.p = b.p and r.r+1 = b.r and r.r < 10000
)
, s as
(
select a, p, max(r) r, min(q1) qF, max(q1) qT,
           min(cF) cF, max(cT) cT, max(q) q
    from r
    group by a, p
 -- order by max(r) desc
)
, n as
( select
    case when q = 'BUA' then '---'
         when p in ('CH0007', 'DB2WK1', 'DBWK1', 'DSNTEP2'
                   ,'YAVFSLN') then '---'
         when q = 'OA1$' then '$'
         when q = 'OA1$ OA1$01 OA1$02' then '$+02'
         when q = 'OA1$01' then '$01'
         when cf = 'AV01' and ct = 'AV02' then 'AV-02'
         when q = 'OA1$01 OA1$02' then '$-02'
         when q = 'OA1$01 OA1$02 OA1$03' then '$-03'
         when q = 'OA1$01 OA1$02 OA1$03 OA1$04' then '$-04'
         when q = 'OA1$01 OA1$02 OA1$03 OA1$04 OA1$05' then '$-05'
         when cF >= 'AV01' and cT <= 'AV10'            then 'AV-10'
         when cF >= 'AV01' and cT <= 'AV30' and q in
          ('OA1$01 OA1$02 OA1$03 OA1$21 OA1$22'
          ,'OA1$01 OA1$02 OA1$03 OA1$28 OA1$29 OA1$30'
          ,'OA1$01 OA1$02 OA1$09 OA1$10 OA1$28 OA1$29 OA1$30'
          ,'OA1$28 OA1$29 OA1$30') then                     'AV%30'
         when cF >= 'AV01' and cT <= 'AV30' and r > 24 then 'AVALL'
         when cF >= 'PC01' and cT <= 'PC16' and r > 12 then 'PCALL'
         when cF >= 'XC00' and cT <= 'XC09' and r = 10 then 'XCALL'
         else '???'
    end neu,
    s.*
    from s
)
select  min(neu), max(neu), count(*),              min(p), max(p),
                               min(cF), max(cT),
                 r, qF, qT, q
    from n
    group by r, q, qF, qT
    order by 6,7,8
  ;x;