Logic problem 1

[sparkbyte]

I am having a problem figuring the AND/OR logic in this query.

This is partly to learn the boolean logic better and to create a query to clean up the tracking Table.

Select 
	Tracking_ID, EmployeeID, MachineName, BoxNumber, FileNumber, TrackingDate, Reship, BoxNumberOriginal
from tblTrackingTable
Where
	BoxNumber = '' OR BoxNumber Is Null
AND
	FileNumber = '' OR FileNumber Is Null
AND
	TrackingDate = '' OR TrackingDate Is Null

Sample output.

34	NULL	NULL	1Z1A1230355615215	.BOX.END.	NULL	NULL	NULL
35	NULL	NULL	1Z1A123R0355615215	.BOX.END.	NULL	NULL	NULL
36	NULL	NULL	1Z1A1230355615215	.BOX.END.	NULL	NULL	NULL

I was trying to get the script to output all rows where
BoxNumber, FileNumber, TrackingDate are either empty or null.

As seen in the output the FileNumber and BoxNumber are not empty or Null.

Where have I gone wronge in the Where clause?

Thanks

John Fuhrman

 

[gmmastros]

I have a rule for where clauses that I think EVERYONE should adopt. Here's the rule...

If you mix and's and or's in a where clause, ALWAYS use parenthesis. No exceptions.

If you follow this rule too, it will make things a lot easier for you.

Where
    [!]([/!]BoxNumber = '' OR BoxNumber Is Null[!])[/!]
AND
    [!]([/!]FileNumber = '' OR FileNumber Is Null[!])[/!]
AND
    [!]([/!]TrackingDate = '' OR TrackingDate Is Null[!])[/!]

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

I always use the same rule as George for OR/AND mix.

PluralSight Learning Library

 

[vikramshah1978]

I agree with George.

U also also read about operator precedence.

 

[sparkbyte]

Thanks guys, George that did it.

vikramshah1978, I will see what I can find on order precedence.

Thanks

John Fuhrman