Writing queries to be shared

To make it possible to share a query with another user, you can use a model query, substitution variables, or the USER variable.

Model query

A model is a copy of a query that contains conditions. With a model query, you or other users can produce different reports by specifying different conditions in a copy of the model.

Suppose, for example, that you are the sales manager of Department 38, and you write a query that lists the name, job, and commission for everyone in your department. The query might look like the one shown in the following figure:

Figure 1. A model query that presents information from the Q.STAFF table
Q.STAFF | NAME      | DEPT   | JOB   | COMM       |
        | P. AO.    | 38     | P.    | P.         |

Other sales managers can get a report for their departments by using your model query. They can display, change, and run the query, or change it and run it later.

Substitution variables

Substitution variables make it possible for other people to use your query. Other users can substitute any value in place of the variable and produce a report specific to their needs.

A substitution variable can represent anything that you can write in a query, such as a column name (except for BINARY, VARBINARY, and XML columns), a search condition, or a specific value. You supply the value for a substitution variable on the RUN command or the RUN Command Prompt panel. You can also specify the substitution variable with a SET GLOBAL command (instead of RUN) before you run the query.

For example, you want a list of the employee names, jobs, and commissions of everyone in each of several different departments. You can construct a query that is generic enough to be used regardless of the department for which you need the information:

Figure 2. Substitution variables in a QBE query
Q.STAFF | NAME      | DEPT   | JOB   | COMM       |
        | P. AO.    | &DEPT  | P.    | P.         |

If you run this query without a value on your RUN command, a prompt panel displays. On the prompt panel, you enter a value to substitute for the variable (&DEPT) in the query. For example, if you specify 38 for the value, QMF reads the query like this:

Q.STAFF | NAME      | DEPT   | JOB   | COMM       |
        | P. AO.    | 38     | P.    | P.         |

The following table shows how you need to specify certain variable values.

Table 1. How to specify substitution variables in QBE
Value that you want to specify for the variable What to enter on the RUN command
A single valid numeric value Specify the value that you want.
Text without embedded quotes, parentheses, blanks, equal signs, or commas Specify exactly.
Text with embedded quotes Enclose the entire value in quotes. (Quotation marks are not removed when QMF makes the substitution.)
Text with embedded parentheses, blanks, equal signs, or commas Enclose the entire value in parentheses. (The outer parentheses are removed when QMF makes the substitution.)

Substitution variable names:

  • Can be no longer than 18 characters, and the first character must be an ampersand (&).
  • Can contain only these characters:
    • Letters of the alphabet
    • National characters: @ # $
    • Special characters:¦ % ? ~ ˋ { } \ | ! ¢
    • Numbers
    • Underscores (_)
  • Can be separated from another variable or command word by any of the characters that are not previously listed, such as commas, blanks, or parentheses.

The USER variable

Another method of sharing a query is to create a query with USER under the NAME column (or any column that contains user identification (user ID) numbers. You can then share the query with other users, who can run it without further changes because their user ID is substituted for the word USER as a condition in the query.