Query filter results vary when using quotes vs. no quotes

[former_member997610]

Greetings,

I am using Webi (BI Platform 4.1 SP4).

I noticed in one of my test queries that the number of rows returned varies depending on whether or not I use single quotes around my constant within the filter.

For example, If my query filter for CPTcode (a dimension field from the universe) indicates > 99201 (a constant), my query returns 5 rows. However, if I modify this to:  >’99201’ , my query then returns 7 rows.

I am guessing that the underlying field is a text field which probably requires quotes for the filter to be accurate, but I want to be sure I understand what is happening here (and thus the reason for this post). In a case like this then, is it possible to know with certitude when to use (or not to use) the single quotes apart from experimenting and discovering the discrepancy in the rows returned. Thanks in advance for explaining this!

[shiva_vishnubatla · Accepted solution]

Hi Dave,

Like you said, the quotes are to be used for character type fields, and the greater than is used in case of Numeric fields (no quotes). 
It really should have failed with data type mismatch or something if you used it either way. 

Try getting the sql and run it directly on a db console with and without quotes and see if you get same result, but in general, if this is a numeric type dimension, then i wouldnt bother with the quotes.

Regards,

shiva