Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
BedrockMission!

Learn more

View all

Sign in to view all badges

SOLVED

Diferent results of Contains vs Does not Contain

Darren_Bowers
Community Advisor
Community Advisor

Hi All - found something weird that we couldn't explain the other day. Testing out a simple query using Contains and the opposite Does not Contain gives different results.

As you can see below, the full data set is 42,356 records. A query of lowercase(first name) that contains the text string "deceased" returns 7 records (this is correct). However, if we apply the query in the opposite fashion it returns 40,053 records. There was an extra  2,296 records it was excluding from the result set. It turns out that these extra records were blank first names.

So my question is why does the query for Does not Contain exclude blank strings in the result? Surely a blank string Does not contain the string "deceased"

Darren_Bowers_0-1604008345654.png

 

query string
1 Accepted Solution
Milan_Vucetic
Correct answer by
Community Advisor
Community Advisor

Hi @Darren_Bowers 

this is not related to AC but to underlying database.

 

Contain/does not contain are translated to this:

SELECT * FROM yourTable R0 WHERE (R0.sFirstName LIKE '%' || 'deceased' || '%' ESCAPE '\')
SELECT * FROM yourTable R0 WHERE (R0.sFirstName NOT LIKE '%' || 'deceased' || '%' ESCAPE '\')

 

If you run these queries directly on database the second query will not count records with empty name string.

 

Regards,

Milan

View solution in original post

3 Replies
Milan_Vucetic
Correct answer by
Community Advisor
Community Advisor

Hi @Darren_Bowers 

this is not related to AC but to underlying database.

 

Contain/does not contain are translated to this:

SELECT * FROM yourTable R0 WHERE (R0.sFirstName LIKE '%' || 'deceased' || '%' ESCAPE '\')
SELECT * FROM yourTable R0 WHERE (R0.sFirstName NOT LIKE '%' || 'deceased' || '%' ESCAPE '\')

 

If you run these queries directly on database the second query will not count records with empty name string.

 

Regards,

Milan

View solution in original post

Darren_Bowers
Community Advisor
Community Advisor
Thanks Milan! Do you know if there is a way to force the database to include blank records when it does the query? Right now I am running the query using the "does contain" and excluding it, which is ok but still two steps I'd like to consolidate. Cheers Darren
Milan_Vucetic
Community Advisor
Community Advisor
Hi @Daren_Bowers, not realy to force but you may use function Coalesce or NVL to replace empty string with some dummy value and then these records will be counted. Check definition of these functions.Regards, Milan