Hi Dmitry,
>>If I change the 10 in the above REPLICATE with 15, no error.
>>
>>REPLICATE(' ',15-LEN(ALLTRIM(REFERENCE)))
>>
Weird errors can occur in VFP SQL Select when a selected expression has variable length. Basically the first encountered instance sets the size of the result field.
Example: if the first selected ALLTRIM(REFERENCE) length is 10 chars, then
REPLICATE(' ',10-LEN(ALLTRIM(REFERENCE)))
yields
REPLICATE(' ',0)
which is a zero-length string.
However, in SQL it appears to generate a one-character result field, presumably because 0 length fields cannot exist. I wonder whether this anomaly is part of the problem, since one character is too short for even one and to be useful you'd need that result to be long enough for the shortest REFERENCE.
If the code used to work, perhaps that was because shorter REFERENCE is encountered first. Was the resultset ordered?
Increasing to 15 may avoid the issue because now the result field is at least 6*5=30 chars rather than an unexpected 1 character.
If this is the explanation, then ordering the resultset or
PADR(REPLICATE(' ',10-LEN(ALLTRIM(REFERENCE))),60)
should prevent the issue.
"... They ne'er cared for us
yet: suffer us to famish, and their store-houses
crammed with grain; make edicts for usury, to
support usurers; repeal daily any wholesome act
established against the rich, and provide more
piercing statutes daily, to chain up and restrain
the poor. If the wars eat us not up, they will; and
there's all the love they bear us."
-- Shakespeare: Coriolanus, Act 1, scene 1