Consultor Eletrônico

Status: Unverified

SYMPTOM(s):

Static query returns different results to a Dynamic query

Dynamic query is using different indexes to the Static query

Static query returns results whilst the Dynamic query does not

Standard FOR EACH returns same results as the Static query

Using same query statement in both the Dynamic and Static queries

Checks are made for empty values in the query statement i.e. customer.name = ""

CAUSE:

Expected behaviour.

Dynamic query statement is encapsulated in quotation marks, and checks are made for empty fields. When encountered in a string, double-quotation marks are compiled as a single quotation mark. So "" becomes ", which changes the query.

Instead of:

a = "...WHERE customer.name = "" AND customer.address = ""..."

the query becomes:

a = "...WHERE customer.name = " AND customer.address = "..."

FIX:

To avoid this problem, implement one of the following options:

1) Enclose the string in single quotes and use double-quotes in the string i.e. ' test = "help" '
2) Use 4 double-quotes which will compile as 2 double-quotes i.e. test = """"
3) Use 2 single quotes i.e. test = ''
4) Place a tilde (~) before each double-quote within the string i.e. test = ~"~"