Forum Stats

  • 3,752,653 Users
  • 2,250,533 Discussions
  • 7,867,907 Comments

Discussions

get records where parent has multiple child records where the parent_amt should be SUM of child amt

mist123
mist123 Member Posts: 156 Bronze Badge

Hi All,

Oracle version 11.2

 create table xxc_invoices
as
(
select 100 invoice_id , 100 inv_amount, 'Y' pay_flag from dual
union all
select 101 invoice_id , 100 inv_amount, 'Y' pay_flag from dual
union all
select 102 invoice_id , 100 inv_amount, 'Y' pay_flag from dual
union all
select 103 invoice_id , 100 inv_amount, 'P' pay_flag from dual
union all
select 104 invoice_id , 100 inv_amount, 'P' pay_flag from dual
);

create table xxc_invoice_pay
as
(
select 100 invoice_id , 100 pay_amt, 0001 check_id, 10 pay_id from dual
union all
select 101 invoice_id , 50 pay_amt, 0002 check_id , 11 pay_id from dual
union all
select 101 invoice_id , 50 pay_amt, 0002 check_id, 12  pay_id from dual
union all
select 102 invoice_id , 50 pay_amt, 0003 check_id , 13 pay_id from dual
union all
select 102 invoice_id , 50 pay_amt, 0004 check_id, 14  pay_id from dual
union all
select 103 invoice_id , 50 pay_amt, 0005 check_id , 15 pay_id from dual
union all
select 104 invoice_id , 50 pay_amt, 0005 check_id, 16  pay_id from dual
);

create table xxc_check_pay
as
(
select 0001 check_id, 'CASHED' check_status from dual
union all
select 0002 check_id, 'CASHED' check_status from dual
union all
select 0003 check_id, 'CASHED' check_status from dual
union all
select 0004 check_id, 'NON-CASHED' check_status from dual
union all
select 0005 check_id , 'CASHED' check_status from dual
);

xxc_invoices.invoice_id=xxc_invoice_pay.invoice_id
xxc_invoice_pay.check_id=xxc_check_pay.check_id

current result:

Expected output:

I need invoice+ one/multiple check_id have CASHED status+invoice_amount=SUM(PAY_AMT)

Ex:

invoice_id 100 (pay_flag should be Y) has only check_id 001 and its invoice_amount= SUM(PAY_AMT) and check_status is CASHED.

invoice_id 101 (pay_flag should be Y) has only check_id 002 and its invoice_amount= SUM(PAY_AMT) and check_status is CASHED.

invoice_id 101 (pay_flag should be Y) has only check_id 002 and its invoice_amount= SUM(PAY_AMT) and check_status is CASHED.

I need only to be populate highlighted rows.

I don't need to populate 102,104,103 invoie_id rows.

Ex:

invoice_id 102 (pay_flag should be Y) has two check_id 003,004 and its invoice_amount= SUM(PAY_AMT) and but the check_status is CASHED and NON-CASHED.

invoice_id 103,104 have pay_flag N.

Thanks

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,682 Black Diamond
    Accepted Answer
    with t as (
               select  i.invoice_id,
                       i.inv_amount,
                       i.pay_flag,
                       ip.pay_amt,
                       cp.check_id,
                       cp.check_status,
                       min(case cp.check_status when 'CASHED' then 1 else 0 end) over(partition by i.invoice_id) all_cashed_flag,
                       sum(ip.pay_amt) over(partition by i.invoice_id) inv_pay_amt
                 from  xxc_invoices i,
                       xxc_invoice_pay ip,
                       xxc_check_pay cp
                 where i.pay_flag = 'Y'
                   and i.invoice_id = ip.invoice_id
                   and cp.check_id = ip.check_id
              )
    select  invoice_id,
            inv_amount,
            pay_flag,
            pay_amt,
            check_id,
            check_status
      from  t
      where all_cashed_flag = 1
        and inv_amount = inv_pay_amt
    /
    
    INVOICE_ID INV_AMOUNT P    PAY_AMT   CHECK_ID CHECK_STAT
    ---------- ---------- - ---------- ---------- ----------
           100        100 Y        100          1 CASHED
           101        100 Y         50          2 CASHED
           101        100 Y         50          2 CASHED
    
    
    SQL>
    

    SY.

    mist123

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,682 Black Diamond
    Accepted Answer
    with t as (
               select  i.invoice_id,
                       i.inv_amount,
                       i.pay_flag,
                       ip.pay_amt,
                       cp.check_id,
                       cp.check_status,
                       min(case cp.check_status when 'CASHED' then 1 else 0 end) over(partition by i.invoice_id) all_cashed_flag,
                       sum(ip.pay_amt) over(partition by i.invoice_id) inv_pay_amt
                 from  xxc_invoices i,
                       xxc_invoice_pay ip,
                       xxc_check_pay cp
                 where i.pay_flag = 'Y'
                   and i.invoice_id = ip.invoice_id
                   and cp.check_id = ip.check_id
              )
    select  invoice_id,
            inv_amount,
            pay_flag,
            pay_amt,
            check_id,
            check_status
      from  t
      where all_cashed_flag = 1
        and inv_amount = inv_pay_amt
    /
    
    INVOICE_ID INV_AMOUNT P    PAY_AMT   CHECK_ID CHECK_STAT
    ---------- ---------- - ---------- ---------- ----------
           100        100 Y        100          1 CASHED
           101        100 Y         50          2 CASHED
           101        100 Y         50          2 CASHED
    
    
    SQL>
    

    SY.

    mist123