0

We use Oracle PeopleSoft on Oracle Database 12. Tables have no foreign key constraints as all foreign key relationships are handled in application code. To be completely clear, I've simplified this for purposes of posting online as in actuality our composite key is 16 fields and it would be really ugly to post here, but let me know if you prefer it and I will. We have 2 tables which share the same key structure, and have a monetary amount in them. The key may or may not exist in one or both tables, however they are supposed to exist and the sums be equal in both tables so I need to list every key. Programmatically there are sometimes issues that prevent this from happening and I have to audit them. Here is a very basic example.

Ledger Transaction Table (Key UNIT, ACCOUNT, FUND)
UNIT     ACCOUNT     FUND     AMOUNT
US1      10001       1100     100.00
US1      10001       1100     110.00
US2      10001       4001     200.00
US3      10001       1000     150.00

Ledger Review Table (Key UNIT, ACCOUNT, FUND)
UNIT     ACCOUNT     FUND     AMOUNT
US1      10001       1100     100.00
US2      10001       4001     200.00
US4      10001       1100       0.00

What I want to see
Ledger Differences
UNIT     ACCOUNT     FUND     SUM_TRANS_AMT  SUM_REVIEW_AMT 
US1      10001       1100     210.00         100.00
US3      10001       1000     150.00           0.00

So here is how I usually handle this. In order to figure out the total differences for our divisions to drill down and perform corrections, I have to list every combination and difference. I start by performing the following.

with SQRY as
(
select distinct UNIT, ACCOUNT, FUND, SUM(AMOUNT) as TRANS_AMT, 0 as REVIEW_AMT 
from LEDGER_TRANS 
group by UNIT, ACCOUNT, FUND 
having SUM(AMOUNT)<>0
UNION ALL 
select UNIT, ACCOUNT, FUND, 0 as TRANS_AMT, SUM(AMOUNT) as REVIEW_AMT 
from LEDGER_REVIEW 
group by UNIT, ACCOUNT, FUND 
having SUM(AMOUNT)<>0
)

Next from those results I perform the following.

select UNION, ACCOUNT, FUND, SUM(TRANS_AMT) AS SUM_TRANS_AMT, SUM(REVIEW_AMT) as SUM_REVIEW_AMT 
from SOURCE_QRY 
group by UNION, ACCOUNT, FUND 
having SUM(TRANS_AMT)<>SUM(REVIEW_AMT)

This works, returning all key combinations from first table, and all key combinations from second table, grouping them together with their resulting sums, and only returning the results where the but is exceptionally slow with hundreds of millions of rows in the transaction table, so I usually break it up into multiple groups such as ACCOUNT LIKE '1%' then ACCOUNT LIKE '2%' and then do other work and check back later. I'm wondering if I'm brute forcing it too much and if there is another function or another practice that I should be using.

3
  • Welcome to dba stackexchange - please have a look at this: Help me write this query in SQL Oct 21 at 14:36
  • Thanks! It looks like I've done and fulfilled most everything on this list, is there something specific you would like me to clarify or include? Oct 22 at 15:04
  • Just some ideas: 1) The distinct in the 1st subselect does nothing because the group by already makes the set distinct, but it could add an extra sort step to the execution. 2) the subselects do a sum and the main select also sums on the same rows, which adds extra steps. I'd try to sum only in the main select, that also allows you to group only on the main select and eliminate another few sort actions.
    – Tony
    Oct 27 at 6:59

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.