Is there any real reason why we have to do this
for each customer where city <> "" and city <> ? no-lock:
And why is there no data type compatibility error when checking character field against ?.
Thanks,
Venky
The special character ? (without quotes) is treated as unknown in ABL language. More information about this you can find here:
documentation.progress.com/.../index.html
Thanks,
Swathi.
The special character ? (without quotes) is treated as unknown in ABL language. More information about this you can find here:
documentation.progress.com/.../index.html
Thanks,
Swathi.
And since nothing is 'less' than an empty string, I will always use:
city > ""
Note:
Normally your application (or DB trigger, constraint) will make sure, there is no ? in the DB in char fields. So code the ? in all queries is kind of paranoia and not helpful in question of clearness and performance.
Why? There is a nice differentiation between 'I do not know the city (yet)' and 'I know there is no city' (which may be a bit strange in the case of an address with a city, but can apply for other fields).
On the performance side a > "" will be able to bracket on an index, whereas <> will not.
> And since nothing is 'less' than an empty string,
It was not true in old Progress versions:
> On the performance side a > "" will be able to bracket on an index, whereas <> will not.
Formally it's a bracketing but in fact it's still a full table scan. The only difference between "<>" and ">" in this case - "<>" will use a primary index while ">" will use an index on the "city" field.
[quote user="Stefan Drissen"]
And since nothing is 'less' than an empty string, I will always use:
city > ""
[/quote]
This can catch you off guard. If city is not indexed, it will select only records with cities and ignore those that are blank or ?.
But ? sorts high in an index, so if city is indexed, the lack of an upper bound causes the bracket to be on the city index, from "greater than blank" to the end of the index, which includes the records containing ?, thus the records with ? will be returned as well.
You can easily see the difference if you run the following example with and without the index definition on the temp-table.
DEFINE TEMP-TABLE tt FIELD cf AS CHARACTER /*INDEX idx cf*/ . CREATE tt. ASSIGN cf = "Koos". CREATE tt. ASSIGN cf = "Jan". CREATE tt. ASSIGN cf = ?. FOR EACH tt WHERE cf > "": DISPLAY tt. END.
This is well documented in the documentation, specifically in "OpenEdge Web Paper: ABL Database Triggers and Indexes" under the heading "Indexes and unknown values".
[quote ]
This is well documented in the documentation, specifically in "OpenEdge Web Paper: ABL Database Triggers and Indexes" under the heading "Indexes and unknown values".
Stefan DrissenAnd since nothing is 'less' than an empty string, I will always use:city > ""
This can catch you off guard. If city is not indexed, it will select only records with cities and ignore those that are blank or ?.
But ? sorts high in an index, so if city is indexed, the lack of an upper bound causes the bracket to be on the city index, from "greater than blank" to the end of the index, which includes the records containing ?, thus the records with ? will be returned as well.
You can easily see the difference if you run the following example with and without the index definition on the temp-table.
DEFINE TEMP-TABLE tt
FIELD cf AS CHARACTER
/*INDEX idx cf*/
.
CREATE tt.
ASSIGN cf = "Koos".
CREATE tt.
ASSIGN cf = "Jan".
CREATE tt.
ASSIGN cf = ?.
FOR EACH tt WHERE cf > "":
DISPLAY tt.
END.
This is well documented in the documentation, specifically in "OpenEdge Web Paper: ABL Database Triggers and Indexes" under the heading "Indexes and unknown values".
Flag this post as spam/abuse.
Hmm... very interesting. I have to admit to usually only using > "" in IF statements - but will need to perform a quick query scan...
To prevent unknown values in DB you can define a field as mandatory.