September 28, 2007 at 1:00 pm
I have two where clauses I am running against a SalesLogix database. For the first one, I get 3709 rows returned. For the second, I get 6265 rows. But they should be essentially the same. Can someone explain why these would get different results?
WHERE (A1.EMAIL IS NOT NULL AND A1.EMAIL <> '' AND A1.DONOTSOLICIT='F')
AND ((UPPER(A1.DEPARTMENT) LIKE '%NEURO%')
OR (UPPER(A1.DEPARTMENT) LIKE '%PSYCHO%')
OR (UPPER(A1.DEPARTMENT) LIKE '%PHYSIOLO%')
OR (UPPER(A1.DEPARTMENT) LIKE '%BEHAVIOR%')
OR (UPPER(A1.DEPARTMENT) LIKE '%COMMUNICA%')
OR (UPPER(A1.DEPARTMENT) LIKE '%FACTOR%')
OR (UPPER(A1.DEPARTMENT) LIKE '%PERFORMANCE%')
OR (UPPER(A4.DEPARTMENT) LIKE '%NEURO%')
OR (UPPER(A4.DEPARTMENT) LIKE '%PSYCHO%')
OR (UPPER(A4.DEPARTMENT) LIKE '%PHYSIOLO%')
OR (UPPER(A4.DEPARTMENT) LIKE '%BEHAVIOR%')
OR (UPPER(A4.DEPARTMENT) LIKE '%COMMUNICA%')
OR (UPPER(A4.DEPARTMENT) LIKE '%FACTOR%')
OR (UPPER(A4.DEPARTMENT) LIKE '%PERFORMANCE%'))
and
WHERE (A1.EMAIL IS NOT NULL AND A1.EMAIL <> '' AND A1.DONOTSOLICIT='F')
AND ((UPPER(A1.DEPARTMENT) LIKE '%NEURO%')
OR (UPPER(A1.DEPARTMENT) LIKE '%PSYCHO%')
OR (UPPER(A1.DEPARTMENT) LIKE '%PHYSIOLO%')
OR (UPPER(A1.DEPARTMENT) LIKE '%BEHAVIOR%')
OR (UPPER(A1.DEPARTMENT) LIKE '%COMMUNICA%')
OR (UPPER(A1.DEPARTMENT) LIKE '%FACTOR%')
OR (UPPER(A1.DEPARTMENT) LIKE '%PERFORMANCE%'))
OR ((UPPER(A4.DEPARTMENT) LIKE '%NEURO%')
OR (UPPER(A4.DEPARTMENT) LIKE '%PSYCHO%')
OR (UPPER(A4.DEPARTMENT) LIKE '%PHYSIOLO%')
OR (UPPER(A4.DEPARTMENT) LIKE '%BEHAVIOR%')
OR (UPPER(A4.DEPARTMENT) LIKE '%COMMUNICA%')
OR (UPPER(A4.DEPARTMENT) LIKE '%FACTOR%')
OR (UPPER(A4.DEPARTMENT) LIKE '%PERFORMANCE%'))
Note that the only difference is the double parenthesis around all of the department fields versus around a1.department and a4.department.
Thanks!
M. Kaney
September 28, 2007 at 1:11 pm
I hope I ready your post correctly - I looked quickly, you have a lot of parenthesis, it is friday, and I am hungry.
To simplify this, here is what you have:
WHERE (Criteria1)
AND (Criteria2 OR Criteria3)
WHERE (Criteria1)
AND (Criteria2)
OR (Criteria3)
The first requires Criteria1 to be true and either Criteria2 or Criteria3.
The second requires Criteria1 to be true and Criteria2 to be true, or just Criteria3 to be true.
September 28, 2007 at 1:27 pm
Thank you! I knew it was just something my Friday brain was missing. 🙂
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply