🚀 go-pugleaf

"Frederic Gelinas" <frederic_gelinas@hotmail.com> wrote in message 
news:4845581a$1@newsgroups.borland.com...
> Frederic Gelinas wrote:
>
>> Select sum(amount) from ARITEM a  left outer join ARTRANSACTION art on
>> art.ID=a.ARTRANSACTIONID
>>   where a.client=:aClient and a.customer=:aCustomer
>>   and ((a.SCANDATE <= :aDate)
>>   and (a.ARTRANSACTIONID is null or art.trandate > :aDate))
>
> That query is not working.  It is not counting any records where
> ARITEM.ARTRANSACTIONID is null.  The left outer join automatically
> rejects those records.
>
> I'm back to square 1.

Ok, the sum(amount) is potentially ambiguous.  I would re-write as 
sum(a.amount) just in case.

Your (a.ARTRANSACTIONID is NULL is a bit redundant; I would revise that too:

Select sum(a.amount)
from ARITEM a
  left outer join ARTRANSACTION art on art.ID=a.ARTRANSACTIONID
where a.client=:aClient and
          a.customer=:aCustomer and
          a.SCANDATE <= :aDate and
          (art.trandate is null or art.trandate > :aDate)

The left outer join will not exclude records where a.ARTRANSACTIONID is 
null -- that's the whole point of using a left outer join.

Dan