zOs/SQL/WI104SQ

$*( generate sql to update twi104h1
       however, the olap expression does need to much sort space ....
$*)
call sqlConnect Dbof
$<>
$** $>. fEdit()
$<=[
with g as
(
  select WI10400T, WI10400D, WI10400L
     , count(*) cnt
    from (select * from oa1p.TWI104H1004
               order by WI10400T, WI10400D, WI10400L
               fetch first 300000 rows only) t
    group by WI10400T, WI10400D, WI10400L
)
, r as
(
  select g.*
      , sum(cnt) over (order by WI10400T, WI10400D, WI10400L) cum
    from g
)
, s as
(
  select r.*
      , value(max(cum)
         over (order by WI10400T, WI10400D, WI10400L
               rows  between 1 preceding and 1 preceding), 0 ) prevCum
from r
    where floor(cum / 100) <> floor((cum - cnt) / 100)
)
select *
    from s
 -- where prev <> prevT || '?' || prevD || '?' || prevL
    order by WI10400T, WI10400D, WI10400L
    with ur
$]
$@. sqlRdr()
$|
$= c = 0
m.cc.1 = WI10400T
m.cc.2 = WI10400D
m.cc.3 = WI10400L
$forWith $@[
    $=c =- $c + 1
    $=c8 =- right($c, 8)

    if $c = 1 then $@[
        $$ -- $c8 from begin to $WI10400T $WI10400D $WI10400L cnt $CUM
        $@sqlBeg
        $@% sqlPred 1 <= '$WI10400T',  '$WI10400D', '$WI10400L'
    $] else $@[
        $$ -- $c8 from $prevT $prevD >$prevL$*(
          $*) to $WI10400T $WI10400D $WI10400L cnt $-[$CUM - $PREVCUM$]
        $@sqlBeg
        if $WI10400T <> $prevT then $@[
            $@% sqlPred 1 >  '$prevT'   , '$prevD'   , '$prevL'
            $@% sqlPred 1 <= '$WI10400T', '$WI10400D', '$WI10400L'
        $] else if $WI10400D <> $prevD then $@[
            $$- "     and" m.cc.1 "= '"$prevT"'"
            $@% sqlPred 2 >               '$prevD'   , '$prevL'
            $@% sqlPred 2 <=              '$WI10400D', '$WI10400L'
        $] else  $@[
            $$- "     and" m.cc.1 "= '"$prevT"'" $*+
                     "and" m.cc.2 "= '"$prevD"'"
            $@% sqlPred 3 >                            '$prevL'
            $@% sqlPred 3 <=                           '$WI10400L'
        $]
    $]
    $@sqlEnd
    $= prevT = $WI10400T
    $= prevD = $WI10400D
    $= prevL = $WI10400L
$]
$** remaining rows
$=c8 =- right($c + 1, 8)
$$ -- $c8 from $prevT $prevD >$prevL to end
$@sqlBeg
$@% sqlPred 1 >  '$prevT'   , '$prevD'   , '$prevL'
$@sqlEnd
$proc $@=/sqlBeg/
select count(*)
      , min(WI10400T || '?' || WI10400D || '?' || WI10400L)
      , max(WI10400T || '?' || WI10400D || '?' || WI10400L)
    from oa1p.TWI104H1004
    where WI104PK <> -9999
$/sqlBeg/

$proc $@=/sqlEnd/
; rollback ;
$/sqlEnd/

$proc $@/sqlPred/
parse arg , b1 op rest
    o1 = left(op, 1)
    o2 = left(op'=', 2)
    li = 'and ('
    d0 = 4
    $do bx=0 $@[
        parse var rest val ',' rest
        cx = bx + b1
        col = m.cc.cx
        if rest = '' then $@[
            $$- left('', d0 + bx*2) li col op strip(val) $*+
                left('', bx * 2 + 1, ')')
            return
            $]
        $$- left('', d0 + bx*2) li col o2 strip(val) $*+
             'and ('col o1 strip(val)
        $$- left('', d0 + 1 + bx*2) 'or ('col '=' strip(val)
        li = 'and'
        $]
$/sqlPred/
$#out                                              20160916 11:39:06