And/or Where statement

Transact-SQL
1

Will this work like I intend it to? I want all wh.partnumber that beginning with 10839 or 10879, and are also in 'K110' status and completed date is null. I'm unsure on my use of brackets around the or statement after the "and".

WHERE ws.WO_STATUS = 'K110'
AND ws.COMPLETED_DATE IS NULL
and (wh.PARTNUMBER like '10839%' or wh.PARTNUMBER like '10879%')

2

You can simplify the query and get rid of the OR:

WHERE   ws.WO_STATUS = 'K110'        
AND ws.COMPLETED_DATE IS NULL        
AND wh.PARTNUMBER LIKE '108[37]9%'

With that said - your query is correct as written.

1 Like
3

Thank you.

4

I think performance may be poor (because of the OR an wildcard in the LIKE - although "Starts with" is usually handled well, whereas a wildcard at the beginging [i.e. a "Contains" test] is more likely to perform badly).

I've had this type of situation where I got better performance with

SELECT Col1, Col2, ...
FROM MyTable
WHERE ws.WO_STATUS = 'K110'
AND ws.COMPLETED_DATE IS NULL
and wh.PARTNUMBER like '10839%'
UNION
SELECT Col1, Col2, ...
FROM MyTable
WHERE ws.WO_STATUS = 'K110'
AND ws.COMPLETED_DATE IS NULL
AND wh.PARTNUMBER like '10879%'

This query is not identical as it will remove Duplicates (if any exist) WITHIN each of the sub-queries, whereas your query would retain any such duplicates.

1 Like
5

Change this to a UNION ALL and it will not eliminate the duplicates - and generally will perform better.

6

Yeah, but that WILL change the behaviour, compared to the original query ...