Combine 2 queries and return one result in columns

Question

Apologize if this was already answered, I looked but couldn't find exactly what I am looking for. I have 2 set of queries

Query 1: Debit Memo Query I want to return 1 debit memo per Account_number (no specific preference which one)
Query 2: Credit Memo Query I want to return 1 credit memo per Account_number (no specific preference which one)the match query 1 and 2 by Account Number and show the result in columns instead of row.

First issue: I need to limit one debit memo per account from query 1 and credit memo per account from query 2. Then match query 1 and query 2 by account number, return the result in columns. Desired result shown in a screenshot below.

Query #1 Debit Memo Query:

Select distinct 
    hca.account_number, hcsu.attribute8, hcsu.attribute9,
    apsa.amount_due_remaining debit_memo_amount, apsa.customer_trx_id, rcta.trx_number
from 
    hz_cust_accounts hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, ra_customer_trx_all rcta, ar_payment_schedules_all apsa, manar.man_om_consignments mac
where 
    1 = 1
    and hca.cust_account_id = hcas.cust_account_id
    and hca.cust_account_id = rcta.bill_to_customer_id
    and rcta.customer_trx_id = apsa.customer_trx_id
    and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
    and hcsu.SITE_USE_CODE = 'BILL_TO'
    and hcsu.status = 'A'
    and ((apsa.amount_due_remaining > 0 and hcsu.attribute9 = 'CUSTOMER REFUND'))
    and mac.CONSIGNMENT_ID = rcta.attribute2
order by 2, 1, 5;

Query #2 Credit Memo Query:

Select distinct 
    hca.account_number, hcsu.attribute8, hcsu.attribute9,
    apsa.amount_due_remaining credit_memo_amount, apsa.customer_trx_id, rcta.trx_number
from 
    hz_cust_accounts hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, ra_customer_trx_all rcta, ar_payment_schedules_all apsa, manar.man_om_consignments mac
where 
    1 = 1
    and hca.cust_account_id = hcas.cust_account_id
    and hca.cust_account_id = rcta.bill_to_customer_id
    and rcta.customer_trx_id = apsa.customer_trx_id
    and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
    and hcsu.SITE_USE_CODE = 'BILL_TO'
    and hcsu.status = 'A'
    and ((apsa.amount_due_remaining < 0 and  hcsu.attribute9= 'CUSTOMER REFUND'))
    and mac.CONSIGNMENT_ID = rcta.attribute2
order by 
    2, 1 , 5;

Desired result:

debit memo and credit memo per account in column result

Admin · Answer

This not a solution and too long to comment.Why not start with customer a/c#12000033 or any customer account number that you know has a credit memo and work backwards and tweak along the way.

 with credit as ( Select distinct hca.account_number,hcsu.attribute8,hcsu.attribute9,apsa.amount_due_remaining,apsa.customer_trx_id, rcta.trx_number
        from hz_cust_accounts hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, ra_customer_trx_all rcta, ar_payment_schedules_all apsa, manar.man_om_consignments mac
        where 1=1
        and hca.cust_account_id = hcas.cust_account_id
        and hca.cust_account_id = rcta.bill_to_customer_id
        and rcta.customer_trx_id = apsa.customer_trx_id
        and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
        and hcsu.SITE_USE_CODE = 'BILL_TO'
        and hcsu.status = 'A'
        and((apsa.amount_due_remaining < 0 and  hcsu.attribute9= 'CUSTOMER REFUND') )
        and mac.CONSIGNMENT_ID = rcta.attribute2
        and hca.account_number= 12000033
        order by 2, 1 , 5),
    debit as (
    Select distinct hca.account_number,hcsu.attribute8,hcsu.attribute9,apsa.amount_due_remaining,apsa.customer_trx_id, rcta.trx_number
    from hz_cust_accounts hca, hz_cust_acct_sites_all hcas, hz_cust_site_uses_all hcsu, ra_customer_trx_all rcta, ar_payment_schedules_all apsa, manar.man_om_consignments mac
    where 1=1
    and hca.cust_account_id = hcas.cust_account_id
    and hca.cust_account_id = rcta.bill_to_customer_id
    and rcta.customer_trx_id = apsa.customer_trx_id
    and hcas.cust_acct_site_id = hcsu.cust_acct_site_id
    and hcsu.SITE_USE_CODE = 'BILL_TO'
    and hcsu.status = 'A'
    and((apsa.amount_due_remaining > 0 and  hcsu.attribute9= 'CUSTOMER REFUND') )
    and mac.CONSIGNMENT_ID = rcta.attribute2
    and hca.account_number= 12000033
    order by 2, 1 , 5)

    select a.*,b.* from  credit a  -- remove unnecessary columns later
    join debit b
    on (a.account_number=b.account_number)
    and (a.amount_due_remaining+b.amount_due_remaining)=0   -- find matching reverse entry

Combine 2 queries and return one result in columns

1 Answers