FlexFiend FlexFiend - 3 months ago 8
SQL Question

T-SQL: Where clause appears to be ignored

I'm working with SQL Server, and it appears to be returning rows that are filtered AGAINST in my predicate. I've narrowed my query down to something that is small but returns the bad data.

DECLARE @ConnectMagicNumber BIGINT;
SET @ConnectMagicNumber = 4294967296;

SELECT DISTINCT
FLOOR(pe.ID_PE/@ConnectMagicNumber) as 'PE-StaNo',
pe.FName, pe.VName
FROM [dbo].[CRM_CPPE] pe
WHERE
-- Predicate below is not being applied
(FLOOR(pe.ID_PE/@ConnectMagicNumber)) > 5000
--
AND pe.FName = 'Augendoppler' OR (pe.FName = 'Batinic' AND pe.VName ='Drasco')


Why on earth is that row with a PE-StaNo of 1 coming back when I explicitly filtered for only ones where with Greater Than 5000.

Any help on what I'm missing would be appreciated. I'm shaken to the core that I can't perform filtering correctly.

Bad SQL Query

Answer

You are missing some parenthesis here. You need to keep in mind the order of operations.

WHERE 
-- Predicate below is not being applied
(FLOOR(pe.ID_PE/@ConnectMagicNumber)) > 5000
--
AND (pe.FName = 'Augendoppler' OR (pe.FName = 'Batinic' AND pe.VName ='Drasco'))