PRB: "SQL Column Not Found" with Macro Substitution in SELECT (118554)



The information in this article applies to:

  • Microsoft Visual FoxPro for Windows 3.0
  • Microsoft FoxPro for Macintosh 2.5b
  • Microsoft FoxPro for Macintosh 2.5c
  • Microsoft FoxPro for Windows 2.5
  • Microsoft FoxPro for Windows 2.5a
  • Microsoft FoxPro for Windows 2.5b
  • Microsoft FoxPro for Windows 2.6
  • Microsoft FoxPro for MS-DOS 2.0
  • Microsoft FoxPro for MS-DOS 2.5
  • Microsoft FoxPro for MS-DOS 2.5a
  • Microsoft FoxPro for MS-DOS 2.5b
  • Microsoft FoxPro for MS-DOS 2.6

This article was previously published under Q118554

SYMPTOMS

The error message below will be generated when you are using macro substitution for the table name in the <alias>.<select_item> clause in a SELECT-SQL statement:
   SQL column <macro variable + table field> not found
				

MORE INFORMATION

Normally macro substitution will not cause a problem when used in the FROM <table> clause. Macro substitution is a good way of using replaceable parameters during program execution, such as when you are specifying that the same field name from several tables should be used as the <select_item>. For example, using the FoxPro 2.x CUSTOMER and INVOICES tables:
   table1='customer'
   table2='invoices'
   SELECT customer.cno, invoices.cno FROM &table1, &table2;
      WHERE customer.cno=invoices.cno INTO CURSOR QUERY
				
However, using all macro substitution in the example below will result in the error message mentioned above:
   SELECT &table1.cno, &table2.cno FROM &table1, &table2;
      WHERE &table1.cno=&table2.cno INTO CURSOR QUERY
				

RESOLUTION

To work around this behavior, insert an extra period (".") between each of the macro-substituted tables and the table field names. For example:
   SELECT &table1..cno, &table2..cno FROM &table1, &table2;
      WHERE &table1..cno=&table2..cno INTO CURSOR QUERY
				
The extra period is required as a terminator for the macro substitution.

Modification Type:MajorLast Reviewed:12/1/2003
Keywords:kberrmsg KB118554