| Subject | Re: [firebird-support] Need Speed Optimization for SQL |
|---|---|
| Author | Vishal Tiwari |
| Post date | 2015-04-16T07:24:31Z |
Hi SET,
Nice to see you here, but I am getting below error after executing your sql, i think group by clause is needed, would you please help?
Error:
Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause)
On Thursday, 16 April 2015 12:48 PM, "Svein Erling Tysvær svein.erling.tysvaer@... [firebird-support]" <firebird-support@yahoogroups.com> wrote:
>I have below SQL and DDL of respective columns used in this SQL.
>
>Below sql takes 6-7 seconds to give the result.
>
>Would it be possible to bring the output time to 1 seconds, coz there are some other operartion I need to perform based on this sql output withing short period of t ime.
>
>SQL:
>
>SELECT
> Sum(CRM_DOCUMENT_ITEMS.QUANTITY) AS Delivered,
> CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
>FROM
> CRM_DOCUMENT_ITEMS
> INNER JOIN CRM_DOCUMENT_HEADER ON CRM_DOCUMENT_ITEMS.FK_DOCUMENT_HEADER = CRM_DOCUMENT_HEADER.PK_DOCUMENT_HEADER
>WHERE
> CRM_DOCUMENT_HEADER.DOCUMENT_TYPE = 1 and CRM_DOCUMENT_ITEMS.FK_JOB_ITEM in (select PK_JOB_ITEMS from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
>GROUP BY
> CRM_DOCUMENT_ITEMS.FK_JOB_ITEM
Hopefully, Rob or Virgos solutions worked, if not try this:
with TMP(PK_JOB_ITEMS) as
(select distinct PK_JOB_ITEMS
from CRM_JOB_ITEMS where FK_JOB = '{8BDDED49-8509-48C1-A169-B7E68A74C230}')
select
Sum(cdi.QUANTITY) AS Delivered,
cdi.FK_JOB_ITEM
from tmp t
join CRM_DOCUMENT_ITEMS cdi on t.PK_JOB_ITEMS = cdi.FK_JOB_ITEM
join CRM_DOCUMENT_HEADER cdh ON cdi.FK_DOCUMENT_HEADER = cdh.PK_DOCUMENT_HEADER
where cdh.DOCUMENT_TYPE = 1
HTH,
Set