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

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.