Re: SQL Where In

On Mon, Sep 26, 2016 at 12:26 PM, CRPence <crpbottle@xxxxxxxxx> wrote:

[Re: Scanning arrays using string techniques]

This would be an issue for what I am doing.

Some of the values will be MLMD, MLDM and MLD.

So scanning for a value of MLD may give a false positive.

With the very simple example shown, the issue of a
false-positive is easily overcome by coding the selection to the actual
size; i.e. as 4-byte elements, a 3-byte value would be searched as the
blank-padded value. So if in the above example the elements are
four-characters each, then when the element 'MLD␢' ["␢" represents the
blank-pad] is desired, then actually code the scalar to locate that value
*with the blank included\mandatory*; e.g. as coded here, using the CHAR
casting scalar, to avoid any issues with visibility of a blank:

stringAsArrayOf4byteElems='MLMDMLDMMLD MORE'
; -- align under 2nd arg/* ...4...8..12..16 */
exec sql set intVariable =
locate( CHAR('MLD', 4), :stringAsArrayOf4byteElems)
; -- result: intVariable=9 /* The desired effect */
exec sql set intVariable =
locate( 'MLD' ), :stringAsArrayOf4byteElems)
; -- result: intVariable=5 /* Undesirable effect */

Your caveat of "With the very simple example shown" is a pretty
significant one. If the value you are searching for happens to not
need any padding, then you run into the same issue with the
searched-for value possibly straddling two elements. Someone already
gave the much-easier-to-read example of an array whose element size is
3 characters and whose actual contents are 'ABC', 'DEF', and 'GHI';
with a search for 'CDE' (properly padded with zero blanks) resulting
in a false positive.[1]

The above approach can also fail if actual array values happen to
contain embedded (not at the end) blanks. While this is admittedly
less common, it's something I've seen in our own data.

So that is what I was trying to guard against by checking the
alignment numerically. But as you've pointed out, that's also not
robust unless you can ensure that the search continues past unaligned
matches.

Which I guess mainly affirms that treating an array as one long string
and then using string-based scanning techniques is generally not the
best idea.

John Y.


[1] If you ensure that the array is defined such that the element size
is longer than the longest possible value, and thus every value will
have at least one blank at the end; and further, you ensure that no
value will contain blanks other than at the end; then yes, this
technique works. But then it is also tantamount to having
blank-delimited data, and thus is covered by the separately discussed
(and much more successful) delimiter-based string-processing
strategies.