On 4 Nov 2015 02:21, "lmanorders" <lmanorders(at)gmail(dot)com> wrote:
>
> I’m attempting to create a report from the join of two tables:
>
> The table layouts are as follows:
> create table custaccts(acctno char(10) PRIMARY KEY, caname varchar(40),
custstat integer, balances numeric(12,2)[5], ...)
> create table distribution(acctno char(10), CONSTRAINT dist_acctno_fkey
FOREIGN KEY (acctno)
> REFERENCES custaccts (acctno), distamt numeric(12,2), ...)
>
> The distribution table can have from 0 to many entries for each acctno in
the custaccts table. I need to create a selection that returns a list of
customer acctno, caname, custstat, sum(balances), and sum(distamt) WHERE
the sum of balances is not equal to the sum of distamts for a given acctno.
>
> I have come up with the following, which is close to working, but the
result does not include entries in the custaccts table if the sum of
balances is greater than zero, but there are no entries for the acctno in
the distribution table.
>
> SELECT acctno, caname, custstat, acctbal, disttotl FROM
> (SELECT ca.acctno, caname, custstat,
balances[1]+balances[2]+balances[3]+balances[4]-balances[5] AS acctbal,
sum(distamt) AS disttotl
> FROM custaccts ca JOIN distribution dr ON ca.acctno = dr.acctno
WHERE ca.acctno >= '01-0001.00’ AND ca.acctno <= ‘02-0500.00’
> AND custstat > 4" GROUP BY ca.acctno ORDER BY ca.acctno) AS adsel
WHERE acctbal <> disttotl
>
> I also tried changing “JOIN” to “LEFT OUTER JOIN”, but that produced the
same result. The result is missing entries from custaccts where the sum of
balances is greater than zero but there are no entries for that acctno in
distribution.
>
Try a full outer join? With a coalesce on the amount columns to replace
nulls with zero?