12. Report-Writer Statements : Report Setup Statements : .Query Statement—Indicate Start of a Query : Variables
 
Share this page                  
Variables
For standard Ingres databases, variable names:
Must begin with a letter
Can use letters, digits, and underscore (_), and otherwise must comply with the valid object naming rules described in Naming and Name Use Conventions.
Cannot match any of the reserved words listed in Reserved Words.
For variable naming conventions in databases that comply with ANSI/ISO Entry SQL‑92 standards, see Object Naming Conventions for ANSI/ISO Entry SQL-92 Compliant Databases.
You can use variables as runtime substitutes for any part of a query (that is, as a field name, table name, or even in where clauses). You indicate variables in a query by preceding the name with a dollar sign ($). For example, you can specify a query as follows:
.query
   select empname, salary, manager
      from emp
      where salary>$minsal
Subsequently, you can invoke the report with a statement such as:
report mydb myrep (minsal = 20000)
If the parenthetical clause contains characters that are treated specially by your operating system (such as parentheses in Windows NT), enclose it in double quotes:
report mydb myrep "(minsal = 20000)"
Report-Writer converts the query to:
... where salary>20000
You can also assign an initial value for the variable using the with value option in the .declare statement. Report-Writer initializes the variable with your assigned value during the loading of the report specification.
If the value of a variable is not assigned on the command line or during the loading of the report specification, Report-Writer prompts you for the value, using the custom string specified in the with prompt option in the variable declaration. If you did not specify a custom string prompt, Report-Writer uses a default prompt.
You are strongly encouraged to define all variables with the .declare statement. Although Report-Writer recognizes any name preceded by a dollar sign ($) as a variable, undeclared variables assume default types and characteristics that are often incompatible with their intended use.
By defining variables with .declare, there is no limit to the ways you can use the variable in your report. You can assign a value to an undeclared variable only by using a command line parameter or by entering a value in response to a runtime prompt. A value for an undeclared variable cannot be specified with a .let statement. In addition, unless the variable has been declared, attempting to pass a parameter with a null value to Report-Writer can produce incorrect results.
Specify as many variables as you want in a query by defining a unique name for each variable. If the same variable is to be substituted more than once within the query, specify the name, prefixed by a dollar sign ($), at each place where substitution is to be done.
Variables can be specified anywhere in the query, or example, within the column list for a select statement or as a value to which the contents of a column is compared. For example, the following query phrases are legal:
... select $var, ... from emp ...
... where name = '$employee_name' ...
If the variable is a character string used for comparison purposes in a where clause, enclose the variable within quotes that are appropriate for your query language (single quotes for SQL), as shown in the preceding example. If you omit these quotes in the query, the user must be made aware of the need to include them in the string value for the variable at runtime.
If you actually want to include the dollar sign ($) as a literal part of the query, precede it with a backslash (\). For example:
... where symbol = '\$' ...
You can also use variables specified in the .query statement in the body of the report. Report-Writer prints the value of the variable if the variable name is preceded by a dollar sign ($). For more information, see the Poplulation Example in the appendix "Report-Writer Report Examples."