Hi SET,
I tried as below way by adding Group By clause:
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
group by cdi.FK_JOB_ITEM
And sql is taking 1.893 seconds....
Woooowwwwwww SET !!!!!! You again saved dear friend......
Hats Off to You as Usual My Dear.....
Any other way to make this more faster ? Please.... If possible....
On Thursday, 16 April 2015 12:54 PM, "Vishal Tiwari
[email protected] [firebird-support]" <[email protected]>
wrote:
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
[email protected] [firebird-support]"
<[email protected]> 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
#yiv7270778608 #yiv7270778608 -- #yiv7270778608ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7270778608
#yiv7270778608ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7270778608
#yiv7270778608ygrp-mkp #yiv7270778608hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv7270778608 #yiv7270778608ygrp-mkp #yiv7270778608ads
{margin-bottom:10px;}#yiv7270778608 #yiv7270778608ygrp-mkp .yiv7270778608ad
{padding:0 0;}#yiv7270778608 #yiv7270778608ygrp-mkp .yiv7270778608ad p
{margin:0;}#yiv7270778608 #yiv7270778608ygrp-mkp .yiv7270778608ad a
{color:#0000ff;text-decoration:none;}#yiv7270778608 #yiv7270778608ygrp-sponsor
#yiv7270778608ygrp-lc {font-family:Arial;}#yiv7270778608
#yiv7270778608ygrp-sponsor #yiv7270778608ygrp-lc #yiv7270778608hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7270778608
#yiv7270778608ygrp-sponsor #yiv7270778608ygrp-lc .yiv7270778608ad
{margin-bottom:10px;padding:0 0;}#yiv7270778608 #yiv7270778608actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7270778608
#yiv7270778608activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7270778608
#yiv7270778608activity span {font-weight:700;}#yiv7270778608
#yiv7270778608activity span:first-child
{text-transform:uppercase;}#yiv7270778608 #yiv7270778608activity span a
{color:#5085b6;text-decoration:none;}#yiv7270778608 #yiv7270778608activity span
span {color:#ff7900;}#yiv7270778608 #yiv7270778608activity span
.yiv7270778608underline {text-decoration:underline;}#yiv7270778608
.yiv7270778608attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv7270778608 .yiv7270778608attach div a
{text-decoration:none;}#yiv7270778608 .yiv7270778608attach img
{border:none;padding-right:5px;}#yiv7270778608 .yiv7270778608attach label
{display:block;margin-bottom:5px;}#yiv7270778608 .yiv7270778608attach label a
{text-decoration:none;}#yiv7270778608 blockquote {margin:0 0 0
4px;}#yiv7270778608 .yiv7270778608bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv7270778608
.yiv7270778608bold a {text-decoration:none;}#yiv7270778608 dd.yiv7270778608last
p a {font-family:Verdana;font-weight:700;}#yiv7270778608 dd.yiv7270778608last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7270778608
dd.yiv7270778608last p span.yiv7270778608yshortcuts
{margin-right:0;}#yiv7270778608 div.yiv7270778608attach-table div div a
{text-decoration:none;}#yiv7270778608 div.yiv7270778608attach-table
{width:400px;}#yiv7270778608 div.yiv7270778608file-title a, #yiv7270778608
div.yiv7270778608file-title a:active, #yiv7270778608
div.yiv7270778608file-title a:hover, #yiv7270778608 div.yiv7270778608file-title
a:visited {text-decoration:none;}#yiv7270778608 div.yiv7270778608photo-title a,
#yiv7270778608 div.yiv7270778608photo-title a:active, #yiv7270778608
div.yiv7270778608photo-title a:hover, #yiv7270778608
div.yiv7270778608photo-title a:visited {text-decoration:none;}#yiv7270778608
div#yiv7270778608ygrp-mlmsg #yiv7270778608ygrp-msg p a
span.yiv7270778608yshortcuts
{font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7270778608
.yiv7270778608green {color:#628c2a;}#yiv7270778608 .yiv7270778608MsoNormal
{margin:0 0 0 0;}#yiv7270778608 o {font-size:0;}#yiv7270778608
#yiv7270778608photos div {float:left;width:72px;}#yiv7270778608
#yiv7270778608photos div div {border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}#yiv7270778608
#yiv7270778608photos div label
{color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7270778608
#yiv7270778608reco-category {font-size:77%;}#yiv7270778608
#yiv7270778608reco-desc {font-size:77%;}#yiv7270778608 .yiv7270778608replbq
{margin:4px;}#yiv7270778608 #yiv7270778608ygrp-actbar div a:first-child
{margin-right:2px;padding-right:5px;}#yiv7270778608 #yiv7270778608ygrp-mlmsg
{font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7270778608
#yiv7270778608ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7270778608
#yiv7270778608ygrp-mlmsg select, #yiv7270778608 input, #yiv7270778608 textarea
{font:99% Arial, Helvetica, clean, sans-serif;}#yiv7270778608
#yiv7270778608ygrp-mlmsg pre, #yiv7270778608 code {font:115%
monospace;}#yiv7270778608 #yiv7270778608ygrp-mlmsg *
{line-height:1.22em;}#yiv7270778608 #yiv7270778608ygrp-mlmsg #yiv7270778608logo
{padding-bottom:10px;}#yiv7270778608 #yiv7270778608ygrp-msg p a
{font-family:Verdana;}#yiv7270778608 #yiv7270778608ygrp-msg
p#yiv7270778608attach-count span {color:#1E66AE;font-weight:700;}#yiv7270778608
#yiv7270778608ygrp-reco #yiv7270778608reco-head
{color:#ff7900;font-weight:700;}#yiv7270778608 #yiv7270778608ygrp-reco
{margin-bottom:20px;padding:0px;}#yiv7270778608 #yiv7270778608ygrp-sponsor
#yiv7270778608ov li a {font-size:130%;text-decoration:none;}#yiv7270778608
#yiv7270778608ygrp-sponsor #yiv7270778608ov li
{font-size:77%;list-style-type:square;padding:6px 0;}#yiv7270778608
#yiv7270778608ygrp-sponsor #yiv7270778608ov ul {margin:0;padding:0 0 0
8px;}#yiv7270778608 #yiv7270778608ygrp-text
{font-family:Georgia;}#yiv7270778608 #yiv7270778608ygrp-text p {margin:0 0 1em
0;}#yiv7270778608 #yiv7270778608ygrp-text tt {font-size:120%;}#yiv7270778608
#yiv7270778608ygrp-vital ul li:last-child {border-right:none
!important;}#yiv7270778608
