Re: SqlPlus question
Date: Mon, 07 Jun 1999 16:44:33 GMT
Message-ID: <7jgste$s27$1_at_nnrp1.deja.com>
Sham,
I was able to "brute force" it with a combination of
Group By and Union Statements...If the database is
large, I am unsure what the performance would be like.
If this is for simple troubleshooting, or checking, then I can see why you would want SQL to produce the requested result. But if the result is for a report, I would think that detail + summation would be more easily handled outside SQL, by the reporting tool.
Nevertheless, it was interesting, and here is my solution:
- Table and Data
Name Null? Type
- -------- ---- ACCOUNT VARCHAR2(10) DEBIT NUMBER CREDIT NUMBER
---------- --------- ---------
a/c1 10 20 a/c1 15 25 a/c2 10 20 a/c2 20 10
I created one account that balances, and one account that does not.
2. Finding only the unequal accounts:
select Account
from Accting
group by account
having sum(debit) != sum(credit);
ACCOUNT
a/c1
3. Putting it all together. Find only unequal accounts and display detail, followed by total, followed by difference:
select
Account||'a' as sortkey, Account, debit, credit
from accting
where account in
(select Account
from Accting
group by Account
having sum(debit) != sum(credit))
union all
select
Account||'b' as sortkey, 'Sum' as Account, sum(debit), sum(credit) from accting
where account in
(select Account
from Accting
group by Account
having sum(debit) != sum(credit))
Group by Account, Account
union all
select
Account||'c' sortkey, 'Difference' as Account, sum(debit)-sum (credit), 0
from accting
where account in
(select Account
from Accting
group by Account
having sum(debit) != sum(credit))
Group by Account, Account
order by 1;
SORTKEY ACCOUNT DEBIT CREDIT ----------- ---------- --------- ---------
a/c1a a/c1 10 20 a/c1a a/c1 15 25 a/c1b Sum 25 45 a/c1c Difference -20 0
(The only way to get rid of the last zero in credit column would be to return ALL credit values as character (To_Char) and send a NULL for the select of "Difference").
In each section of the Union ALL clause, each select performs the same sub-select to gather ONLY the rows where inequality exists.
Good Luck,
Robert Proffitt
Beckman Coulter
Brea, California
RTProffitt_at_beckman.com
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Received on Mon Jun 07 1999 - 18:44:33 CEST