zOs/SQL/WP11103

set current application compatibility 'V11R1';
with p as
(
  select case when WP111_DEPOT_TEC <= '0835013883975000' then 01
              when WP111_DEPOT_TEC <= '0835037335635005' then 02
              when WP111_DEPOT_TEC <= '0835055796585006' then 03
              when WP111_DEPOT_TEC <= '0835066396225015' then 04
              when WP111_DEPOT_TEC  || ' ' || WP111_VALOR_TEC
                                   <= '0835066396665000 001480005000'
                                                         then 05
              when WP111_DEPOT_TEC <= '0835067398205001' then 06
              when WP111_DEPOT_TEC <= '0835085584415002' then 07
              when WP111_DEPOT_TEC <= '0835094226475000' then 08
              when WP111_DEPOT_TEC <= '0835119461005002' then 09
              when WP111_DEPOT_TEC <= '0835144186355000' then 10
              when WP111_DEPOT_TEC <= '0835160650715000' then 11
              when WP111_DEPOT_TEC <= '0835172777735000' then 12
              when WP111_DEPOT_TEC  || ' ' || WP111_VALOR_TEC
                                   <= '0835182961785000 001221405000'
                                                         then 13
              when WP111_DEPOT_TEC <= '0848070578545002' then 14
              when WP111_DEPOT_TEC <= '0883079543695000' then 15
              else 16 end p
       , WP111_DEPOT_TEC || ' ' || WP111_VALOR_TEC depVal
  from OA1P.TWP111A103
)
select p, count(*)
        , min(depVal)
        , max(depVal)
  from p
  group by p
  with ur
;x;
with d as
(
  select count(*) cnt
       , WP111_DEPOT_TEC  || ' ' || WP111_VALOR_TEC depVal
  from OA1P.TWP111A103
  where wp111_depot_tec
          between '0835172778975000' and  '0848070578545002'
  group by WP111_DEPOT_TEC, WP111_VALOR_TEC
--fetch first 1000 rows only
)
, t as
(
  select sum(cnt) t, count(*) d
    from d
)
, r as
(
  select d.*
      , sum(cnt) over(order by depVal
                 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) r
    from d
)
, p as
(
  select floor(2. * (r-1) / t) p, sum(cnt) cnt, count(d) dep
    , min(r) rFr, max(r) rTo
    , min(depVal) depValFr
    , max(depVal) depValTo
    , t.*
    from r, t
    group by floor(2. * (r-1) / t), t.t, t.d
)
select p.*
    from p
    with ur
;x;
set current application compatibility 'V11R1';
with d as
(
  select count(*) cnt
       , WP111_DEPOT_TEC depot
  from OA1P.TWP111A103
  group by WP111_DEPOT_TEC
--fetch first 100 rows only
)
, t as
(
  select sum(cnt) t, count(*) d
    from d
)
, r as
(
  select d.*
      , sum(cnt) over(order by depot
                 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) r
    from d
)
, p as
(
  select floor(16. * (r-1) / t) p, sum(cnt) cnt, count(d) dep
    , min(r) rFr, max(r) rTo
    , min(depot) depFr, max(depot) depTo
    , t.*
    from r, t
    group by floor(16. * (r-1) / t), t.t, t.d
)
select p.*
    from p
    with ur
;x;
select p, count(*)
        , min(r), max(r)
        , min(depot), max(depot)
with p as
(
  select case when WP111_DEPOT_TEC <= '0835006999999999' then 01
              when WP111_DEPOT_TEC <= '0835012099999999' then 02
              when WP111_DEPOT_TEC <= '0835017899999999' then 03
              when WP111_DEPOT_TEC <= '0835025399999999' then 04
              when WP111_DEPOT_TEC <= '0835033499999999' then 05
              when WP111_DEPOT_TEC <= '0835042999999999' then 06
              when WP111_DEPOT_TEC <= '0835052999999999' then 07
              when WP111_DEPOT_TEC <= '0835066396664999' then 08  --w2
              when WP111_DEPOT_TEC <= '0835066396665000' then 09  --w2
              when WP111_DEPOT_TEC <= '0835085269999999' then 10
              when WP111_DEPOT_TEC <= '0835088999999999' then 11  --orig
              when WP111_DEPOT_TEC <= '0835096499999999' then 12  --orig
              when WP111_DEPOT_TEC <= '0835147850425000' then 13  --v1
              when WP111_DEPOT_TEC <= '0835174940655001' then 14  --v1
              when WP111_DEPOT_TEC <= '0858144681905000' then 15  --v1
  --          when WP111_DEPOT_TEC <= '0835094152965065' then 11  --w2
  --          when WP111_DEPOT_TEC <= '0835136302155000' then 12  --w2
  --          when WP111_DEPOT_TEC <= '0835161723405000' then 13  --w2
  --          when WP111_DEPOT_TEC <= '0835182961785000' then 14  --w2
  --          when WP111_DEPOT_TEC <= '0881024343795006' then 15  --w2
  --orig      when WP111_DEPOT_TEC <= '0835057999999999' then 08  --orig
  --orig      when WP111_DEPOT_TEC <= '0835072999999999' then 09  --orig
  --orig      when WP111_DEPOT_TEC <= '0842054999999999' then 13  --orig
  --orig      when WP111_DEPOT_TEC <= '0862053999999999' then 14  --orig
  --orig      when WP111_DEPOT_TEC <= '0882999999999999' then 15  --orig
              else 16 end p
       , WP111_DEPOT_TEC depot
  from OA1P.TWP111A103
)
, r as
(
  select p.*
      , row_number() over(order by depot) r
    from p
)
select p, count(*)
        , min(r), max(r)
        , min(depot), max(depot)
   from r
   group by rollup (p, floor(r / 1e6))
   order by 3, 4 desc
   with ur