On Wed, 29 Apr 2015 13:11:47 +0200, 'Stef' [email protected]
[firebird-support] <[email protected]> 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
[email protected]
