Subject | Re: SQL Case query takes 4 minutes 30 seconds to execute !! |
---|---|

Author | Virgo Pärna |

Post date | 2015-04-30T08:04:18Z |

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

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@...

> No it should only return 1 line but even with “and jtype = j.jtype” added toMy question was specifically about inner part of the query - that:

> the subselect condition it still takes almost 5 minutes to return data

>

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@...