March 26, 2018 at 2:11 pm
I think i need to put my OR in (), but not sure how . . query runs longer than it should..
I want SITE_ID to be LIke 'AP%' OR Like 'G9%' then the other ANDs.
HAVING
Q.PERIOD_NUM='2017_01_12'
AND Q.SITE_ID Like 'AP%'
Or Q.SITE_ID Like 'G9%'
AND Q.SUB_ORG ='FORD'
AND Q.QUAL_CTRY_CODE='US'
AND Q.TRADE_PROGRAM = 'NAFTA'
as always, thanks . . .
March 26, 2018 at 2:34 pm
HAVING
Q.PERIOD_NUM='2017_01_12'
AND Q.SITE_ID Like 'AP%'
Or Q.SITE_ID Like 'G9%'
AND Q.SUB_ORG ='FORD'
AND Q.QUAL_CTRY_CODE='US'
AND Q.TRADE_PROGRAM = 'NAFTA'
The OR probably should be enclosed as below:HAVING
Q.PERIOD_NUM='2017_01_12'
AND (Q.SITE_ID Like 'AP%'
Or Q.SITE_ID Like 'G9%')
AND Q.SUB_ORG ='FORD'
AND Q.QUAL_CTRY_CODE='US'
AND Q.TRADE_PROGRAM = 'NAFTA'
Although I don't have much to go on, why are you doing this in the HAVING clause rather than the WHERE clause? It's obviously a group, but conditions in the HAVING clause can't be determined until all the groupings are done. None of these criteria are related to a grouping. Usually you'd see something like HAVING SUM(Sales)>10000. All of these look like they should be applied to the individual transaction. This should help your query go faster because the WHERE criteria will be evaluated before the grouping starts.
March 26, 2018 at 2:42 pm
Ron, thanks!
Funny you mentioned why the original coder used a HAVING clause when they weren't "HAVING" anything that was being grouped.
There are a couple aggregates in the Select: Sum(M.TOT_EXT_COST) AS SumOfTOT_EXT_COST,
Sum(M.TOT_EXT_QTY) AS SumOfTOT_EXT_QTY,
but nothing being Summed is in the HAVING . . .
I thought the exact same thing, but i am sure i got lucky!
Thanks!!!
March 26, 2018 at 3:02 pm
You're welcome. And your comment made me realize I should have added a SUM in my example. I've updated it accordingly.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply