zOs/SQL/WBFM701

$#@
call sqlConnect dbaf
call sqlStmts $>.fEdit('::v') $<=[
declare global temporary table session.pg
    ( pg char(8) not null
    , lv int not null
);
create unique index session.iPg   on session.pg
    (pg);
insert into session.pg values('FIVP10B', 0);
$@do i=1 to 23 $@=[
insert into session.pg
    select c.ref_name, lv+1
        FROM GDB0671.TWBFM701 c, session.pg p
        where name = p.pg and ISCALLED = 'Y'
        and not exists (select 1 from session.pg q
                                 where q.pg = c.ref_name)
        group by c.ref_name, lv
;
$]
with q as
(   select lv, (select count(*)
                    FROM GDB0671.TWBFM701 c
                    where c.name = p.pg and c.ISCALLED = 'Y') c
        from session.pg p
)
select lv, count(*) total,
        sum(case when c = 0 then 1 else 0 end) "noC",
        sum(case when c = 0 then 0 else 1 end) "wiC",
        sum(c) ch
    from q
    group by lv
    order by lv
;
select * from session.pg p
    where not exists (select 1
                    FROM GDB0671.TWBFM701 c
                    where c.name = p.pg and c.ISCALLED = 'Y')
;
$]
$#end
with uni as
(
    select name, ref_name, min(ref_type) ref_type, count(*) cop
    FROM GDB0671.TWBFM701
    where ISCALLED = 'Y'
     -- and  not (ref_name like 'YY%' or ref_name like 'YX%')
    group by name, ref_name --, ref_type
)
, P1  (
         nm,
         LF, LT)
      AS
 (SELECT
         NAME,
         0, 0
    FROM uni
   WHERE NAME                        = 'FIVP10B'
    UNION ALL
   SELECT
          C.ref_NAME,
          P.LF, p.lt + 1
     FROM uni C
        , P2 P
    WHERE P.nm                        = C.NAME
      AND P.Lt                        <  3
  )
, p2 as (
    select nm, min(lf) lf, max(lt) lt
        from p1
        group by nm
  )
   select * from p2     ;;;
   select lvl, count(*)
      from parent
      group by lvl
      order by lvl
;;;
with uni as
(
    select name, ref_name, min(ref_type) ref_type, count(*) cop
    FROM GDB0671.TWBFM701
    where ISCALLED = 'Y'
        and  not (ref_name like 'YY%' or ref_name like 'YX%')
    group by name, ref_name --, ref_type
)
, PARENT  (
         REF_NAME,
         REF_TYPE,
         LVL,
         cop,
         PATH )
      AS
 (SELECT
         REF_NAME,
         REF_TYPE,
         0,
         cop,
         varchar(name || ' ' || ref_name || ' ', 400)
    FROM uni
   WHERE NAME                        = 'FIVP10B'
    UNION ALL
   SELECT
          C.REF_NAME,
          C.REF_TYPE,
          P.LVL + 1,
          c.cop,
          P.PATH !! C.ref_name || ' '
     FROM uni C
        , PARENT P
    WHERE P.REF_NAME                  = C.NAME
      AND P.LVL                       <  14
      AND LOCATE(' ' || C.REF_NAME || ' ' , P.PATH) < 1
  )
   select * from parent ;;;
   select lvl, count(*)
      from parent
      group by lvl
      order by lvl
;;;
select *
    from gdb0671.twbfm701
    where name =    'FIVP10B'
;                          ;
select count(*), count(distinct name), count(distinct ref_name)
    FROM GDB0671.TWBFM701
    with ur
;
select name, count(*)
    FROM GDB0671.TWBFM701
    where ISCALLED = 'Y'
    group by name
    order by 2 desc
;
select ref_name, count(*)
    FROM GDB0671.TWBFM701
    where ISCALLED = 'Y'
    group by ref_name
    order by 2 desc
;;;
   select lvl, count(*), ref_name, min(ref_type), max(ref_type), path
       from parent
       where ref_name = 'YCZU458'
       group by lvl, ref_name, path
       order by 2 desc
 ;;,
   select ref_name, count(*), min(lvl), max(lvl)
      from parent
      group by ref_name
      order by 2 desc
;;:
select *
    from gdb0671.twbfm701
    where name =    'FIVP10B'