On Wed, 29 Apr 2015 13:11:47 +0200, 'Stef' stef@... [firebird-support] <firebird-support@yahoogroups.com> wrote:

> No it should only return 1 line but even with “and jtype = j.jtype” added to
> the subselect condition it still takes almost 5 minutes to return data
>

My question was specifically about inner part of the query - that:
select (jdate) as jdate,
(Select COALESCE(sum(debitamount),0 )
......
part.

I guess, the main question is, what is the expected result of the query.
When I'm looking at it, my first question would be: what is the actuall
reason of using subselects for getting field value instead of GROUP BY.
If adding jtype=j.jtype does not make query invalid, then shouldn't

select jdate, source2, jtype, sum(coalesce(debitamount, 0)) as paid, sum(coalesce(creditamount, 0)) as invoicetotal
from journal j
where j.jdate >= '2015/04/28' and j.accnr = 5995100 and j.SUPPID = '1' and (j.jtype = 2 or j.jtype = 99)
group by jdate, source2, jtype

would have same result?

Which means I could rewrite entire query:

select distinct
sum(case when jdate between '2015-04-01' and '2015-04-30' then invoicetotal-paid else 0 end) as Current_days,
sum(invoicetotal-paid) as totaldue
from
(
select jdate, source2, jtype, sum(coalesce(debitamount, 0)) as paid, sum(coalesce(creditamount, 0)) as invoicetotal
from journal j
where j.jdate >= '2015/04/28' and j.accnr = 5995100 and j.SUPPID = '1' and (j.jtype = 2 or j.jtype = 99)
group by jdate, source2, jtype
)
having sum(invoicetotal-paid) <> 0
order by 2 asc


--
Virgo Pärna
virgo.parna@...