add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table bills ( id primary key, bgroup, bnumber, bvalue, partner )
as
select 1, 1, 111, 10.90, null from dual union all
select 2, 1, 751, 40.28, null from dual union all
select 3, 1, 438, 125.60, null from dual union all
select 4, 1, 659, -10.90, 987 from dual union all
select 5, 1, 387, -165.88, 755 from dual union all
select 6, 1, 774, -100.10, null from dual union all
select 7, 1, 664, -80.12, null from dual union all
select 8, 1, 259, 180.22, 999 from dual union all
select 9, 2, 774, -200.10, null from dual union all
select 10, 2, 664, -80.12, null from dual union all
select 11, 2, 259, 280.22, 777 from dual ;
11 rows affected
create table minuses as
select id
, bgroup as mgroup
, bnumber as mnumber
, bvalue * -1 as mvalue
, partner as mpartner
from bills where bvalue < 0 ;
6 rows affected
create table pluses as
select id
, bgroup as pgroup
, bnumber as pnumber
, bvalue as pvalue
, partner as ppartner
from bills where bvalue >= 0 ;
5 rows affected
create or replace view splitminuses
as
with recursiveclause ( nextid, pgroup, tvalue, componentid )
as (
select -- anchor member
id as nextid
, pgroup as pgroup
, pvalue as tvalue -- total value
, to_char( id ) as componentid
from pluses
union all
select -- recursive member
P.id
, R.pgroup
, R.tvalue + P.pvalue
, R.componentid || ',' || to_char( P.id )
from recursiveclause R
join pluses P
on P.id > R.nextid and P.pgroup = R.pgroup -- only look at values in the same group
)
select
pgroup
, tvalue as minusvalue
, componentid as plusids
, mpartner
from
recursiveclause R
, lateral ( select mpartner from minuses M where R.tvalue = M.mvalue ) -- fetch the partner id
where
tvalue in ( select mvalue from minuses where mpartner is not null ) -- get all relevant pvalues that must be broken down into components
and mpartner is not null -- do this for all pluses that have a partner id
;
select * from splitminuses ;
PGROUP MINUSVALUE PLUSIDS MPARTNER
1 10.9 1 987
1 165.88 2,3 755
create or replace view splitpluses
as
with recursiveclause ( nextid, mgroup, tvalue, componentid )
as (
select -- anchor member
id as nextid
, mgroup as mgroup
, mvalue as tvalue -- total value
, to_char( id ) as componentid
from minuses
union all
select -- recursive member
M.id
, R.mgroup
, R.tvalue + M.mvalue
, R.componentid || ',' || to_char( M.id )
from recursiveclause R
join minuses M
on M.id > R.nextid and M.mgroup = R.mgroup -- only look at values in the same group
)
--
select
mgroup
, tvalue as plusvalue
, componentid as minusids
, ppartner
from
recursiveclause R
, lateral ( select ppartner from pluses P where R.tvalue = P.pvalue ) -- fetch the partner id
where
tvalue in ( select pvalue from pluses where ppartner is not null ) -- get all relevant pvalues that must be broken down into components
and ppartner is not null -- do this for all pluses that have a partner id
;
select * from splitpluses;
MGROUP PLUSVALUE MINUSIDS PPARTNER
1 180.22 6,7 999
2 280.22 9,10 777
create table allcomponents ( type_, group_, value_, cids_, partner_ )
as
select 'components of PLUS' as type_, M.* from splitminuses M
union all
select 'components of MINUS', P.* from splitpluses P
;
4 rows affected
select * from allcomponents ;
TYPE_ GROUP_ VALUE_ CIDS_ PARTNER_
components of PLUS 1 10.9 1 987
components of PLUS 1 165.88 2,3 755
components of MINUS 1 180.22 6,7 999
components of MINUS 2 280.22 9,10 777
create table gapfillers
as
select unique type_, group_, value_
, trim( regexp_substr( cids_, '[^,]+', 1, level ) ) cids_
, partner_
from (
select type_, group_, value_, cids_, partner_
from allcomponents
) AC
connect by instr( cids_, ',', 1, level - 1 ) > 0
order by group_, partner_ ;
7 rows affected
select * from gapfillers ;
TYPE_ GROUP_ VALUE_ CIDS_ PARTNER_
components of PLUS 1 165.88 2 755
components of PLUS 1 165.88 3 755
components of PLUS 1 10.9 1 987
components of MINUS 1 180.22 6 999
components of MINUS 1 180.22 7 999
components of MINUS 2 280.22 10 777
components of MINUS 2 280.22 9 777
select
B.id, bgroup, bnumber, bvalue
, case
when B.partner is null then G.partner_
else B.partner
end as partner
from bills B
left join gapfillers G on B.id = G.cids_
order by 1 ;
ID BGROUP BNUMBER BVALUE PARTNER
1 1 111 10.9 987
2 1 751 40.28 755
3 1 438 125.6 755
4 1 659 -10.9 987
5 1 387 -165.88 755
6 1 774 -100.1 999
7 1 664 -80.12 999
8 1 259 180.22 999
9 2 774 -200.1 777
10 2 664 -80.12 777
11 2 259 280.22 777