>>>I've got a class method that builds a SQL statement on the fly based on metadata. The field list has to be built dynamically before issuing the SQL call, as in:
>>>
>>>SELECT &lcFieldList ;
>>> FROM &lcTable ;
>>> INTO CURSOR lvNormalized
>>>
>>>The field list has to be dynamically built from a table of field mappings to normalize the data before moving it to a data warehouse. It all works fine, the hard part is done. But early on in testing I found that the lcFieldList variable can end up exceeding 254 characters, which generates an error. I've gotten around it in a cheesy kind of way, but I was wondering if anyone had any ideas on a more permanent solution.
>>>
>>>TIA
>>
>>Is it exceding 254 Characters or 254 Fields? Cause a string variable can contain like 16 million characters.
>>
>>BOb
>
>Yes, but there is a limit to the length of a command that can be executed with Macro substitution. Don't remember exactly, but 254 sounds like a good candidate.

It would never (I know, never say never) exceed 254 fields, but the macro substituted variable containing the field list does on occasion exceed 254. What I did was create an array of 10 elements. As I go to add to the string that is macro expanded, I check to see if it will exceed the 254 limit. If it does, I go to the next element in the array and start adding to it. Then in my SQL statement, I macro expand each element. The elements that don't have any value expand to '' since I initialize them that way. I just thought it was kludgy, but it seems the only way given the limitation of a len of a command executed with Macro substitution.

FWIW, the code is as follows:

DIMENSION laFldStmnt(10)
laFldStmnt(1) = ''
laFldStmnt(2) = ''
laFldStmnt(3) = ''
laFldStmnt(4) = ''
laFldStmnt(5) = ''
laFldStmnt(6) = ''
laFldStmnt(7) = ''
laFldStmnt(8) = ''
laFldStmnt(9) = ''
laFldStmnt(10) = ''
lnFldStmnt = 1

SCAN
lcFldStmnt = ALLTRIM(Field_Name) + " AS " + ALLTRIM(Master_fld) + ", "
IF LEN(laFldStmnt(lnFldStmnt)) + LEN(laFldStmnt(lnFldStmnt)) > 254
* Too big, go to the next item in the array
lnFldStmnt = lnFldStmnt + 1
ENDIF
laFldStmnt(lnFldStmnt) = laFldStmnt(lnFldStmnt) + lcFldStmnt
ENDSCAN

* Trim the , off the end of the last statement
laFldStmnt(lnFldStmnt) = LEFT(laFldStmnt(lnFldStmnt), LEN(laFldStmnt(lnFldStmnt)) - 2)
lcFrom = ALLTRIM(This.cSource)
lcInto = ALLTRIM(This.cResults)

SELECT ;
&laFldStmnt(1) ;
&laFldStmnt(2) ;
&laFldStmnt(3) ;
&laFldStmnt(4) ;
&laFldStmnt(5) ;
&laFldStmnt(6) ;
&laFldStmnt(7) ;
&laFldStmnt(8) ;
&laFldStmnt(9) ;
&laFldStmnt(10) ;
FROM &lcFrom ;
&lcWhere ;
INTO CURSOR &lcInto

I just thought it looked sloppy to me.