How to Use Wildcards and Macro Substitution in SELECT-SQL

ID: Q110920

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

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

Additional query words: FoxMac FoxDos FoxWin memvar wild card

Keywords          : FxprgSql 
Version           : 2.50 2.50a 2.50b | 2.00 2.50 2.5
Platform          : MACINTOSH MS-DOS WINDOWS 
Issue type        :

Microsoft KB Archive/110920

Contents

How to Use Wildcards and Macro Substitution in SELECT-SQL

SUMMARY

MORE INFORMATION

REFERENCES