Operator "one of" Vs using AND

Dan Beacon

Morning all,


I have built a query which tries to find records which have 2 specfic solicit codes.


Initially i built the query using Solcit code description = X AND solicit Code = Y


I thought this would show me every record which had both solcit code X and Y. But the query came up blank.


I then created a query using Solicit code one of X & Y and ticked the box for "Each record must meet all specified criteria" and this worked and returned the records I was expecting to see.


In my mind, the two scenarios are doing the same thing but that obcvisouly isn;t correct! Could anyone provide any insight on why the 1st way using the AND function didn;t work?


Cheers


Dan

Alan French

My understanding is that when you query for "Solicit Code = X AND Solicit Code = Y", RE is checking to see whether each solicit code (not each constituent) meets those criteria, which isn't possible since no single code can be both X and Y.

Lynnette Fisher

Morning Dan,


When you use X And Y as the criteria and then marked must meet all criteria, it is my understanding that the query is looking ONLY for those records that have both those solicit codes and no others. Using One of has the query look for those records that have either solicit codes.


Hope that makes sense...it is early and no coffee on board yet?.

Dan Beacon

Thanks for your help Alan

Dan Beacon

Thanks Lynnette

Christine Cooke

You if are looking for both solicit codes have to be on the records that come up then it should be AND.

If you are looking for either X or Y solicit code to be on each record that comes up then it should be OR or ONE OF... Solicit Code = X OR Solicit Code =Y. or Solicit Code ONE OF... X new tline Y

David Tang

Hello Dan,


Yep! Like you, sometimes I got trapped thinking the whole picture, rather than thinking linearly like a computer which is one record at a time. What I mean is that the query is actually not checking all the codes at the same time; but it is testing one code at a time. So your query condition needs to reflect the same way.


Say, a constituent has 3 solicit codes (namely, Z, Y, X)...


Your Scenario #1:

Does the 1st solicit code (Z in this case) satisfy the Query Condition of "SOLICIT_CODE=X AND SOLICIT_CODE=Y" ? Obviously "No".

Does the 2nd solicit code (Y in this case) satisfy the Query Condition of "SOLICIT_CODE=X AND SOLICIT_CODE=Y" ? Well, since we know you can't be X and Y at the same time, so "No" - it dose not meet the criteria.

Does the 3rd solicit code (X in this case) satisfy the Query Condition of "SOLICIT_CODE=X AND SOLICIT_CODE=Y" ? Same thing "No" like above.

= > The query result comes up empty.


Your Scenario #2:

Does the 1st solicit code (Z) satisfy the Query Condition of "SOLICIT_CODE IS ONE OF (X, Y)" ? Obviously "No".

Does the 2nd solicit code (Y) satisfy the Query Condition of "SOLICIT_CODE IS ONE OF (X, Y)" ? "Yes"! This solicit code meets the criteria.

Does the 3rd solicit code (X) satisfy the Query Condition of "SOLICIT_CODE IS ONE OF (X, Y)" ? Same thing "Yes" like above.

= > The query pulls this constituent in the result!


Hope this quick illustration helps!