Re: SQL how to fix

On 30 Oct 2012 14:49, Stone, Joel wrote:

<<SNIP>>
where translate(left(amtxt,65),'+','0123456789','+')
like '%+++++++++%'

In the old thread "Subject: SQL select records with numeric strings"
http://archive.midrange.com/midrange-l/201209/threads.html#00612
I seem to recall that the above WHERE clause was described as intending to select rows where there is a 9-digit string. I do not recall anyone explicitly mentioning that the use of the plus sign can effect more false-positives than when using a digit into which to translate all digits; though Rob mentioned using the digit '1' instead of the '+' character.

FWiW for amtxt='the value +12345678 is not a 9-digit account number', that row would be selected even though only an 8-digit number is in that string. Using the following predicate would resolve that type of false-positive issue, although there would remain a problem for longer digits-only strings [e.g. a 10-digit number] that could still result in false-positives:

translate(left(amtxt,65),'9','012345678','9') like '%999999999%'

FWiW a UDF such as a "regex" function or a function specific to the task, could eliminate the use of the [effective or actual] TRANSLATE scalar in the query. That would bypass the CQE requirement\restriction diagnosed by the sqlcode=-255, thus allowing the OLAP query to be processed with success using the SQE.