January 10, 2018 at 4:12 pm
after 40 minutes, none of the three conditions were returned in my results . . . and i had to stop the query . . .
Where
(H.Importer = N'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE N'98%'
AND L.Part_Num NOT LIKE N'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND
(L.HTS_NUM NOT LIKE ISNULL(C.[CLASS_VALUE_1], '')
OR
L.HTS_NUM NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')
OR
C.[CLASS_VALUE_1] NOT LIKE ISNULL(Z.[COMPOSITE_PART], ''))
The first part of the Where clause is my standard pull, but now i want to see ANY of three conditions in the bottom section with the ORs.
L doesnt equal table C;
OR
L doesnt equal table Z
or table c doesnt equal table Z.
Tables C and/or Z could be NULL . . . i want the nulls also.
thoughts?
January 10, 2018 at 8:20 pm
jeffshelix - Wednesday, January 10, 2018 4:12 PMafter 40 minutes, none of the three conditions were returned in my results . . . and i had to stop the query . . .Where
(H.Importer = N'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE N'98%'
AND L.Part_Num NOT LIKE N'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND
(L.HTS_NUM NOT LIKE ISNULL(C.[CLASS_VALUE_1], '')
OR
L.HTS_NUM NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')
OR
C.[CLASS_VALUE_1] NOT LIKE ISNULL(Z.[COMPOSITE_PART], ''))The first part of the Where clause is my standard pull, but now i want to see ANY of three conditions in the bottom section with the ORs.
L doesnt equal table C;
OR
L doesnt equal table Z
or table c doesnt equal table Z.Tables C and/or Z could be NULL . . . i want the nulls also.
thoughts?
My thoughts:
1) I sure hope your data types are actually unicode and not ASCII. Those leading N's will kill you otherwise.
2) Regardless of working or not, that is going to be a difficult predicate. NOT LIKEs, ORs, and functions around columns in the WHERE clause. The last is the second worst thing I see in aggregate in my 25ish years of tuning SQL Server as a consultant. (#1 above - i.e. mismatched datatypes - is the worst if you were wondering).
3) You almost certainly need parentheses around ALL of your OR clauses - as in before the first (L. and after the last '')).
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 11, 2018 at 7:17 am
Thanks for the advice! I will give them a shot . . . thanks again
Jeff
January 11, 2018 at 7:45 am
You might also try the alternate way to write that criteria:
and not
(
L.HTS_NUM =ISNULL(C.[CLASS_VALUE_1], '') and
L.HTS_NUM = ISNULL(C.[CLASS_VALUE_1], '') and
--note you can see this one isn't really needed now that you rewrote
C.[CLASS_VALUE_1] =ISNULL(Z.[COMPOSITE_PART], '')
)
(Based on predicate logic rules around NOT).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 11, 2018 at 7:49 am
removing the N's sped the query up CONSIDERABLY.. Under two minutes now compared to 30 before.
However, i get the same results even after adding the extra ().
New Where Clause:
Where
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND
((L.HTS_NUM NOT LIKE ISNULL(C.[CLASS_VALUE_1], '')
OR
L.HTS_NUM NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')
OR
C.[CLASS_VALUE_1] NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')))
i am still getting rows where L, C and Z are the same.
those are the ones I DON'T want.
thoughts again, much appreciated.
January 11, 2018 at 8:01 am
jeffshelix - Thursday, January 11, 2018 7:49 AMremoving the N's sped the query up CONSIDERABLY.. Under two minutes now compared to 30 before.However, i get the same results even after adding the extra ().
New Where Clause:
Where
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND
((L.HTS_NUM NOT LIKE ISNULL(C.[CLASS_VALUE_1], '')
OR
L.HTS_NUM NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')
OR
C.[CLASS_VALUE_1] NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')))i am still getting rows where L, C and Z are the same.
those are the ones I DON'T want.thoughts again, much appreciated.
To clarify are you trying to ensure that all 3 values are distinct? As of now your syntax will allow any 2 of the L, Z or C columns to be the same, as long as the 3rd one is not.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 11, 2018 at 10:16 am
thanks for the post - -
i have three tables, let's say each has a price for the identical item.
In a perfect world all three tables should contain the same price.
I want to know anytime the three prices are not the same.
whether it be
table L doesn't equal table C
OR
Table L doesnt = table Z
OR
Table Z doesnt = table C
the only rows i do NOT want returned are the rows where L=C=Z.
thanks again
January 11, 2018 at 10:22 am
follow up . . . my report pull thousands for rows where L=C=Z also
January 11, 2018 at 1:46 pm
I cheated, i think legally.........I repeated the entire where clause, with an AND between each of my three table matches.
This worked, no appreciable time hit.
Would love to know the CLEAN way to do it . . .
thank
Where
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND ((L.HTS_NUM NOT LIKE ISNULL(C.[CLASS_VALUE_1], '')
and
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND L.HTS_NUM NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')
and
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND C.[CLASS_VALUE_1] NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')))
January 11, 2018 at 2:02 pm
jeffshelix - Thursday, January 11, 2018 1:46 PMI cheated, i think legally.........I repeated the entire where clause, with an AND between each of my three table matches.
This worked, no appreciable time hit.
Would love to know the CLEAN way to do it . . .
thankWhere
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND ((L.HTS_NUM NOT LIKE ISNULL(C.[CLASS_VALUE_1], '')
and
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND L.HTS_NUM NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')
and
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND C.[CLASS_VALUE_1] NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')))
Don't have time to really look at this but some of your NOT LIKE's are just <>'s:
Where
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN' -- This is really L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z') -- This is really Substring(L.Part_Num,4,1) <> 'Z'
AND ((L.HTS_NUM NOT LIKE ISNULL(C.[CLASS_VALUE_1], '') -- This is really this L.HTS_NUM <> ISNULL(C.[CLASS_VALUE_1], '')
and
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN' -- This is really L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z') -- This is really Substring(L.Part_Num,4,1) <> 'Z'
AND L.HTS_NUM NOT LIKE ISNULL(Z.[COMPOSITE_PART], '') -- This is really thie L.HTS_NUM <> ISNULL(Z.[COMPOSITE_PART], '')
and
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN' -- This is really L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z') -- This is really Substring(L.Part_Num,4,1) <> 'Z'
AND C.[CLASS_VALUE_1] NOT LIKE ISNULL(Z.[COMPOSITE_PART], ''))) -- This is really this C.[CLASS_VALUE_1] <> ISNULL(Z.[COMPOSITE_PART], '')
January 11, 2018 at 4:10 pm
Glad the mismatched datatypes worked out. Like I said - biggest performance problem in total in all my consulting. 😎
I don't have time to dig into it, plus my brain is fried, but I think NOT LIKE (or NOT EQUALS) and ORs need different handling per boolean logic.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
January 11, 2018 at 4:23 pm
Lynn Pettis - Thursday, January 11, 2018 2:02 PMjeffshelix - Thursday, January 11, 2018 1:46 PMI cheated, i think legally.........I repeated the entire where clause, with an AND between each of my three table matches.
This worked, no appreciable time hit.
Would love to know the CLEAN way to do it . . .
thankWhere
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND ((L.HTS_NUM NOT LIKE ISNULL(C.[CLASS_VALUE_1], '')
and
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND L.HTS_NUM NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')
and
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z')
AND C.[CLASS_VALUE_1] NOT LIKE ISNULL(Z.[COMPOSITE_PART], '')))Don't have time to really look at this but some of your NOT LIKE's are just <>'s:
Where
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN' -- This is really L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z') -- This is really Substring(L.Part_Num,4,1) <> 'Z'
AND ((L.HTS_NUM NOT LIKE ISNULL(C.[CLASS_VALUE_1], '') -- This is really this L.HTS_NUM <> ISNULL(C.[CLASS_VALUE_1], '')
and
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN' -- This is really L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z') -- This is really Substring(L.Part_Num,4,1) <> 'Z'
AND L.HTS_NUM NOT LIKE ISNULL(Z.[COMPOSITE_PART], '') -- This is really thie L.HTS_NUM <> ISNULL(Z.[COMPOSITE_PART], '')
and
(H.Importer = 'FORD'
AND H.Entry_Summary_Date >= '12/1/2017'
AND H.Entry_Summary_Date < '1/1/2018'
AND L.HTS_Num NOT LIKE '98%'
AND L.Part_Num NOT LIKE 'NPN' -- This is really L.Part_Num <> 'NPN'
AND H.Com_Desc_Code = 'Parts'
AND Substring(L.Part_Num,4,1) Not Like 'Z') -- This is really Substring(L.Part_Num,4,1) <> 'Z'
AND C.[CLASS_VALUE_1] NOT LIKE ISNULL(Z.[COMPOSITE_PART], ''))) -- This is really this C.[CLASS_VALUE_1] <> ISNULL(Z.[COMPOSITE_PART], '')
(Edit - for the OP - not so much a response to Lynn).
Well - at this point you've made it work and you made it closer to "fast", Just be sure to save what you have here before you try to make it "pretty" 😀
Glad that switching OR's to ANDs cleared up your issue (not the same logic outcome as before, but now it's right ).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply