zOs/SQL/BEDIST
with s1 as
( select count(*) s1Rows,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, VERDTYP, sammel1
from OA1A01.VBE010A1 a
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, VERDTYP, sammel1
)
, vr as
( select count(*) vrRows,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, VERDTYP
from OA1A01.VBE010A1 a
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, VERDTYP
)
, sv as
( select s1Rows, vrRows,
s1.PROCBASE, s1.TECHBEZ, s1.DAT_BUCH,
s1.DAT_VAL, s1.VERDTYP, s1.sammel1
from s1, vr
where s1.procBase = vr.procBase
and s1.techBez = vr.techBez
and s1.dat_Buch = vr.dat_buch
and s1.dat_Val = vr.dat_val
and s1.verdtyp = vr.verdtyp
)
, va as
( select sum(s1Rows) s1Rows, sum(vrRows) vrRows,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, sammel1
from sv
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, sammel1
)
, bu as
( select count(*) buRows,
sum(case when sammel1 < '1900-01-01-00:00:00'
then 1 else 0 end) buS0Rows,
sum(case when sammel1 > '1900-01-01-00:00:00'
then 1 else 0 end) buS1Rows,
sum(case when sammel2 > '1900-01-01-00:00:00'
then 1 else 0 end) buS2Rows,
PROCBASE, TECHBEZ, DAT_BUCH
from OA1A01.VBE010A1 a
group by PROCBASE, TECHBEZ, DAT_BUCH
)
, s as
( select s1Rows, vrRows, buRows, buS0Rows, buS1Rows, buS2Rows,
va.PROCBASE, va.TECHBEZ, va.DAT_BUCH, va.DAT_VAL, va.sammel1
from va, bu
where va.procBase = bu.procBase
and va.techBez = bu.techBez
and va.dat_Buch = bu.dat_buch
)
select count(*) cnt,
sum(s1Rows) s1Rows, min(s1Rows) s1RowF, max(s1Rows) s1RowT,
sum(vrRows) vrRows, min(vrRows) vrRowF, max(vrRows) vrRowT,
sum(buRows) buRows, min(buRows) buRowF, max(buRows) buRowT
from s
where procBase between 3300 and 3400
group by floor(log(3*max(s1Rows, 0.1)))
, floor(log(3*max(s1Rows, 0.1)))
, floor(log(3*max(vrRows, 0.1)))
order by 7 desc, 4 desc
;;;;;,,
select * from s
where procBase between 3300 and 3400
order by vrRows desc, s1Rows desc
fetch first 100 rows only
;;;;;,,
select s1.*, bu.*
from s1, bu
where s1.procBase = bu.procBase
and s1.techBez = bu.techBez
and s1.dat_Buch = bu.dat_buch
and s1.verdtyp = bu.verdtyp
order by buRows desc, rows desc
fetch first 100 rows only
with ur
;;;
, va as
( select count(*) vaCnt,
max(
sum(rows) vrRows,
sum(case when sammel1 < '1900-01-01-00:00:00'
then rows else 0 end) vrS0Rows,
sum(case when sammel1 > '1900-01-01-00:00:00'
then rows else 0 end) vrS1Rows,
max(case when sammel1 > '1900-01-01-00:00:00'
then rows else -1999999999 end) vrS1RowsMax,
min(case when sammel1 > '1900-01-01-00:00:00'
then rows else +1999999999 end) vrS1RowsMin,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, VERDTYP
from s1
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, VERDTYP
)
with g as
(
select count(*) rows,
sum(case when sammel1 < '1900-01-01-00:00:00'
then 0 else 1 end) sam1,
sum(case when sammel2 < '1900-01-01-00:00:00'
then 0 else 1 end) sam2,
sum(case when sammel1 < '1900-01-01-00:00:00'
or sammel2 < '1900-01-01-00:00:00'
then 0 else 1 end) sam12,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
, sammel1
from OA1P01.VBE010A1 a
where procBase between 3300 and 3303
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
, sammel1
)
, k as
( select count(*) gCnt,
sum(rows) rows, min(rows) rowsMin, max(rows) rowsMax,
sum(sam1) sam1, min(sam1) sam1Min, max(sam1) sam1Max,
sum(sam2) sam2, min(sam2) sam2Min, max(sam2) sam2Max,
sum(sam12) sam12, min(sam12) sam12Min, max(sam12) sam12Max,
verdTyp
from g
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
)
, s as
( select count(*) kCnt,
sum(gCnt) gCnt, min(gCnt) gCntF, max(gCnt) gCntT,
sum(rows) rows, min(rows) rowsF, max(rows) rowsT,
sum(rowsMin) rowsMin, min(rowsMin) rowsMinF, max(rowsMin) rowsMinT,,
sum(rowsMax) rowsMax, Max(rowsMax) rowsMaxF, max(rowsMax) rowsMaxT,,
sum(sam1) sam1, min(sam1) sam1F, max(sam1) sam1T,
sum(sam1Min) sam1Min, min(sam1Min) sam1MinF, max(sam1Min) sam1MinT,,
sum(sam1Max) sam1Max, Max(sam1Max) sam1MaxF, max(sam1Max) sam1MaxT,,
sum(sam2) sam2, min(sam2Min) sam2MinF, max(sam2Max) sam2MaxT, ,
sum(sam12) sam12, min(sam12Min) sam12MinF, max(sam12Max) sam12MaxT,,
verdTyp
from k
group by verdTyp
, floor(log(3*max(rowsMax, 0.1)))
, floor(log(3*max(sam1Max, 0.1)))
, floor(log(3*max(sam1Min, 0.1)))
)
select *
from s
order by 1, 5 desc, 4 desc
with ur
;;;
with a as
(
select count(*) cnt,
sum(case when sammel1 < '1900-01-01-00:00:00'
then 0 else 1 end) sam1,
sum(case when sammel2 < '1900-01-01-00:00:00'
then 0 else 1 end) sam2,
sum(case when sammel1 < '1900-01-01-00:00:00'
or sammel2 < '1900-01-01-00:00:00'
then 0 else 1 end) sam12,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
from OA1P01.VBE010A1 a
where procBase between 3300 and 3309
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
)
, r1 as
( select count(*) c,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
from OA1P01.VBE010A1
where procBase between 3300 and 3309
and sammel1 > '1900-01-01-00:00:00'
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
, sammel1
)
, s1 as
( select count(*) s1Cnt,
sum(c) s1Sum,
max(c) s1Max,
min(c) s1Min,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
from r1
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, verdTyp
)
, j as
(select a.verdTyp
, cnt, sam1, sam2, sam12
, value(s1Cnt, 0) s1Cnt
, value(s1Sum, 0) s1Sum
, value(s1Max, 0) s1Max
, value(s1Min, 0) s1Min
, a.PROCBASE, a.TECHBEZ, a.DAT_BUCH, a.DAT_VAL
from a
left join s1
on s1.procbase = a.procbase
and s1.techBez = a.techbez
and s1.dat_buch = a.dat_Buch
and s1.dat_val = a.dat_val
and s1.verdTyp = a.verdTyp
)
select verdTyp
, count(*) cnt, sum(cnt) "sumRows"
, max(cnt) "rowsMax", max(s1Max) "s1MaxMax"
, min(cnt) "rows-", max(cnt) "-rows"
, min(sam1) "sam1-", max(sam1) "-sam1"
, min(sam2) "sam2-", max(sam2) "-sam2"
, min(sam12) "sam12-", max(sam12) "-sam12"
, min(s1Cnt) "s1Cnt-", max(s1Cnt) "-s1Cnt"
, min(s1Sum) "s1Sum-", max(s1Sum) "-s1Sum"
, min(s1Max) "s1Max-", max(s1Max) "-s1Max"
, min(s1Min) "s1Min-", max(s1Min) "-s1Min"
from j
group by verdTyp
, floor(log(3*max(cnt, 0.1)))
, floor(log(3*max(s1Max, 0.1)))
, floor(log(3*max(s1Min, 0.1)))
order by 1, 5 desc, 4 desc
with ur
;;;
with a as
(
select count(*) cnt,
sum(case when sammel1 < '1900-01-01-00:00:00'
then 0 else 1 end) sam1,
sum(case when sammel2 < '1900-01-01-00:00:00'
then 0 else 1 end) sam2,
sum(case when sammel1 < '1900-01-01-00:00:00'
or sammel2 < '1900-01-01-00:00:00'
then 0 else 1 end) sam12,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
from OA1P01.VBE010A1 a
where procBase between 3300 and 3309
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
)
, r1 as
( select count(*) c,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
from OA1P01.VBE010A1
where procBase between 3300 and 3309
and sammel1 > '1900-01-01-00:00:00'
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
, sammel1
)
, s1 as
( select count(*) s1Cnt,
sum(c) s1Sum,
max(c) s1Max,
min(c) s1Min,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
from r1
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
)
, j as
(select cnt, sam1, sam2, sam12
, value(s1Cnt, 0) s1Cnt
, value(s1Sum, 0) s1Sum
, value(s1Max, 0) s1Max
, value(s1Min, 0) s1Min
, a.PROCBASE, a.TECHBEZ, a.DAT_BUCH, a.DAT_VAL
from a
left join s1
on s1.procbase = a.procbase
and s1.techBez = a.techbez
and s1.dat_buch = a.dat_Buch
and s1.dat_val = a.dat_val
)
select count(*) cnt, sum(cnt) "sumRows"
, max(cnt) "rowsMax", max(s1Max) "s1MaxMax"
, min(cnt) "rows-", max(cnt) "-rows"
, min(sam1) "sam1-", max(sam1) "-sam1"
, min(sam2) "sam2-", max(sam2) "-sam2"
, min(sam12) "sam12-", max(sam12) "-sam12"
, min(s1Cnt) "s1Cnt-", max(s1Cnt) "-s1Cnt"
, min(s1Sum) "s1Sum-", max(s1Sum) "-s1Sum"
, min(s1Max) "s1Max-", max(s1Max) "-s1Max"
, min(s1Min) "s1Min-", max(s1Min) "-s1Min"
from j
group by floor(log(max(cnt, 0.1)))
, floor(log(max(s1Max, 0.1)))
order by 4 desc, 3 desc
with ur
;;;
with a as
( select
case when sammel1 < '1900-01-01-00:00:00'
then ' ' else '1' end
|| case when sammel2 < '1900-01-01-00:00:00'
then ' ' else '2' end sam,
verdTyp
from OA1P01.VBE010A1
where procBase = 111
)
select count(*), sam, verdTyp
from a
group by sam, verdTyp
order by verdTyp
with ur
;;;
with a as
(
select count(*) cnt,
sum(case when sammel1 < '1900-01-01-00:00:00'
then 0 else 1 end) sam1,
sum(case when sammel2 < '1900-01-01-00:00:00'
then 0 else 1 end) sam2,
sum(case when sammel1 < '1900-01-01-00:00:00'
or sammel2 < '1900-01-01-00:00:00'
then 0 else 1 end) sam12,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
from OA1A01.VBE010A1 a
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
)
, r1 as
( select count(*) c,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
from OA1A01.VBE010A1
where procBase = 1
and sammel1 > '1900-01-01-00:00:00'
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
, sammel1
)
, s1 as
( select count(*) s1Cnt,
sum(c) s1Sum,
max(c) s1Max,
min(c) s1Min,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
from r1
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
)
select cnt, sam1, sam2, sam12
, s1Cnt, s1Sum, s1Max, s1Min
, a.PROCBASE, a.TECHBEZ, a.DAT_BUCH, a.DAT_VAL
from a
left join s1
on s1.procbase = a.procbase
and s1.techBez = a.techbez
and s1.dat_buch = a.dat_Buch
and s1.dat_val = a.dat_val
where a.procBase = 1
order by cnt desc, sam2 desc, sam1 desc
;;;
order by 1 desc
fetch first 100 rows only
where PROCBASE = 1
)
( select from (select count(*) c
left join
select a.*,
from a
with ur
;;;
select *
from OA1A01.VBE010A1 a
where procbase = 1
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
)
, b as
(
select count(*) c,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, sammel1, sammel2
from OA1A01.VBE010A1
where procbase = 1
and (sammel1 > '1900-01-01-00:00:00'
or sammel2 > '1900-01-01-00:00:00')
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, sammel1, sammel2
order by 1 desc
fetch first 100 rows only
with ur
;;;
select *
from OA1A01.VBE010A1
where procbase = 1 and sammel1 > '1900-01-01-00:00:00'
order by sammel1 asc
fetch first 100 rows only
with ur
;;;;
select count(*), sammel1, sammel2,
PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL, sammel1, sammel2
, sammeltyp, verdtyp, sammelbegriff
from OA1A01.VBE010A1
where procbase = 1 and sammel1 > '1900-01-01-00:00:00'
group by PROCBASE, TECHBEZ, DAT_BUCH, DAT_VAL
, sammel1, sammel2, sammeltyp, verdtyp, sammelbegriff
order by sammel1 asc
fetch first 100 rows only
with ur