>I have a macro substitution in an SQL statement as:
>
>SELECT &gcSelect FROM ....
>
>The SQL is bombing because the string being used in gcSelect is 391 characters long. Is this a limitation on macro subs in SQL or is this a limitation in VFP itself. I notice that if I try to evaluate the variable gcSelect in the debugger, it is truncated. Again, didn't know if this was a limitation on the watch/locals window or VFP itself.
>
I've built variables that have been several hundred characters in length and used macro expansion without significant trouble; i intensely dislike using macro expansion as a portion of a SQL Select, having run into far too many problems. As a result, if I find a need to use macro expansion with a SQL Select statement, I create the ENTIRE statement in a memvar and expand it, rather than trying to do it selectively, especially if several sections require macro expansion.
The obvious question is what is the error meesage you receive, and what is the total line of code involved?
Things to test - try to build the line containing the macro expansion in a file and see if it compiles and executes in place of the macro-expansion, If the same line can be executed if it's presented as a literal line of code without expansion, then the problem lies in macro expansion. If not, you'd beeter look at the statement that would be used to see what the problem is there.
If the line of code compiles and executes, then try assigning the statement in it's entirety to a single string variable, and try to macro-expand that string to execute it. If that works, use my preferred method of handling the problem.
If it's a problem with the complexity of the WHERE clause, the complexity supported can be adjusted with the SYS(3055) function; it won't have any effect on the field selection portion, burt may help with complex WHERE and clauses, especially with subqueries. See the on-line docs (or better, read the section in the Hacker's Guide if you have it.)
>Any thoughts?