Microsoft KB Archive/110920

{|
 * width="100%"|

-

The information in this article applies to:


 * Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b
 * Microsoft FoxPro for MS-DOS, versions 2.0, 2.5, 2.5a, 2.5b
 * Microsoft FoxPro for Macintosh, version 2.5b

-

SUMMARY
Using macro substitution or wildcards in a SELECT-SQL statement allows the data to be defined, extracted, and accessed dynamically.

Macro substitution is used when an ampersand symbol (&) precedes a character-type memory variable or array element. The contents of the variable or element replace the macro reference prior to the variable or array element being translated in the SELECT-SQL statement.

The asterisk symbol (*) represents a wildcard reference in a SELECT-SQL statement. The primary use of wildcards in a SELECT-SQL statement is to reference multiple field names in a file without having to list each field individually in the SELECT-SQL statement.

MORE INFORMATION
The following program example uses macro substitution to dynamically change the query output generated by the SELECT-SQL statement. The macro substitution is in the SQL WHERE clause.

  CLEAR ACCEPT "Enter a two character state " TO M_State M_Cust_St = "UPPER(customer.state)" M_EQ = "=" M_State = "'"+UPPER(M_State)+"'" M_Key = M_Cust_St + " " + M_EQ + " " + M_State SELECT customer.company, customer.contact, customer.state ; FROM customer ; WHERE &M_Key ; INTO CURSOR EX_SQL BROWSE TIMEOUT 10 DISPLAY MEMORY LIKE M_* NOTE: When you are using macro substitution for the entire SELECT-SQL statement in a stand-alone executable file, for example   mselect = "SELECT * FROM CUSTOMER" &mselect the macro must be addressed before the macro or command is translated. To resolve this issue and avoid possible errors, include the necessary source code in a separate procedure file in the project prior to compiling the executable file, as shown in the following program example:    PROCEDURE NotUsed SELECT customer.company, customer.contact, customer.state ; FROM customer ; WHERE customer.state = "NC" ; INTO CURSOR EX_SQL If the NotUsed procedure is NOT added to the project, the user will receive a "Feature not available" error message at run time.

Note that if you are using macro substitution in one or more clauses of the SELECT statement, for example   mwhere = "STATE = 'NC'" SELECT * FROM CUSTOMER WHERE &mwhere the NotUsed procedure is NOT required.

The following program uses a wildcard to dynamically reference multiple field names in a SELECT-SQL statement. The following query contains all fields used in both the CUSTOMER.DBF and INVOICES.DBF files that have matching CNO keys.    SELECT * ; FROM customer,invoices ; WHERE customer.cno = invoices.cno ; INTO CURSOR EX_SQL1 BROWSE TIMEOUT 10 Optionally, you can use a wildcard with a work area alias by entering the filename, a period, and then an asterisk. The following SELECT-SQL statement contains all fields used in the CUSTOMER file (which is suffixed with an asterisk); the CNO and SALESMAN fields are displayed as output from the INVOICES.DBF file for all records that contain matching CNO keys in both files.    SELECT customer.*,invoices.cno,invoices.salesman ; FROM customer,invoices ; WHERE customer.cno = invoices.cno ; INTO CURSOR EX_SQL2