Hi Tim,

Here's the test database :-
http://files.pioneersoftware.co.uk/temp/SampleDB.7z

I have a number of routines designed to get back summary information about a
person's finances.

Here's a simple query I use to get back the SUMs of their payments, credits,
invoices and refunds. I do it ONE query (instead of 4 separate queries) to
try to minimize traffic back-and-forth to the db engine :-

--------------------
select 0 as idx, coalesce(sum(pay.amount),0) from pay where
per_id=1000000010000000004
union
select 1 as idx, coalesce(sum(credit.total),0) from credit where
per_id=1000000010000000004
union
select 2 as idx, coalesce(sum(inv.total),0) from inv where
per_id=1000000010000000004
union
select 3 as idx, coalesce(sum(refund.amount),0) from refund where
per_id=1000000010000000004
--------------------

Using the attached sample database, the person in question only has 2
invoices (one for £20, the other for £25) totaling £45 on their account (no
other financial records are present).

The above query returns this :-
----------------
idx | sum()
2    | 0
----------------

However, executing the following SQL on its own :-

select 2 as idx, coalesce(sum(inv.total),0) from inv where
per_id=1000000010000000004

....correctly returns :-
----------------
idx | sum()
2    | 45
----------------

In other words, it's only screwed up when it's part of a UNION query...


To make matters worse, I thought the problem might be to do with the
coalescing, so I tried removing the COALESCE statements only to get the
following :-
----------------
idx | sum()
2    | 20
2    | 25
----------------

There's clearly something very wrong with all this - hope you can help!

Regards,

Hedley.

Hedley

Are there login details for this db ?


John

Hedley

> --------------------
> select 0 as idx, coalesce(sum(pay.amount),0) from pay where
> per_id=1000000010000000004
> union
> select 1 as idx, coalesce(sum(credit.total),0) from credit where
> per_id=1000000010000000004
> union
> select 2 as idx, coalesce(sum(inv.total),0) from inv where
> per_id=1000000010000000004
> union
> select 3 as idx, coalesce(sum(refund.amount),0) from refund where
> per_id=1000000010000000004
> --------------------

If you group by idx it does produce the correct value for rows with records
(it sould not need it).  Also, if there are no records, it should still
produce a row with the idx and 0.

John

Hedley,

<< There's clearly something very wrong with all this - hope you can help!
>>

We're already aware of the issue and it's been fixed for B11.  The issue is
that the WHERE clauses are binding to the first query's table instead of the
query for which it is defined, and this causes very bizarre results, of
course.

--
Tim Young
Elevate Software
www.elevatesoft.com