How to Use Wildcards and Macro Substitution in SELECT-SQL

• 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_*

   mselect = "SELECT * FROM CUSTOMER"
   &mselect

    PROCEDURE NotUsed
    SELECT customer.company, customer.contact, customer.state ;
      FROM customer ;
      WHERE customer.state = "NC" ;
      INTO CURSOR EX_SQL

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 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

    SELECT customer.*,invoices.cno,invoices.salesman ;
      FROM customer,invoices ;
      WHERE customer.cno = invoices.cno ;
      INTO CURSOR EX_SQL2

REFERENCES

FoxPro for MS-DOS "Developer's Guide," version 2.0, page D16-11 FoxPro for MS-DOS "Commands & Functions," version 2.0, page C3-10 FoxPro for MS-DOS and Windows "Language Reference," version 2.5, page L3-9 FoxPro for MS-DOS "Developer's Guide," version 2.5, page D14-11