12. Report-Writer Statements : Report Setup Statements : .Query Statement for QUEL Users : Description
 
Share this page                  
Description
The .query statement indicates the start of a valid QUEL query that creates the data to be reported. All range statements needed to designate the row markers for the query must be specified. This query follows the same rules as any other QUEL query, although it can also contain parameters. You can use as many lines as you need to specify the query—sreport detects the end of the query by the start of a new report formatter statement.
Either the .query or the .data statement (but not both) must be specified for every report. Because the .query statement generates a standard Ingres query, the usual limits of 1024 columns and a maximum row width of 2008 bytes apply to any query issued through the Report-Writer.
If any column you specify with retrieve all is of an unsupported data type, such as long varchar, byte, byte varying, and long byte, Report-Writer silently ignores and does not print any values for that column. If you explicitly specify a column with an unsupported data type in the column_list of a retrieve statement in a query, Report-Writer additionally issues a warning message. If it encounters any subsequent reference to a column with an unsupported data type, regardless of the method used to select the column, Report-Writer issues an error message and terminates the report.
You can use the optional retrieve (resultcolumn=table.columname) construct to reference a column in subsequent statements by a name other than its database table column name. For example, if you retrieved B_column=table1.A_column, in subsequent statements refer to the column as B_column.
Only one .query statement is permitted for a report, and only one data retrieval statement is permitted within the .query statement.
You cannot have both a .query with an order by or sort by clause and a .sort statement in the same report specification, because their functions are mutually exclusive.
Neither delimited identifiers nor the schema.objectname construct are allowed in QUEL queries.
String constants must be enclosed by the standard QUEL string delimiter, the double quote ("). The double quote string delimiter is required only within the .query statement; within other Report-Writer statements, the string delimiter is the single quote.
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.
Variables in the Query
You can use variables as runtime substitutes for any part of a query (that is, field names, table names, or even 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
        range of e is emp
        retrieve (e.empname,e.salary,e.manager)
        where e.salary > $minsal
Subsequently, you can invoke the report with something like the following:
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 and UNIX or slashes in VMS), enclose it in double quotes:
report mydb myrep "(minsal = 20000)"
Report-Writer converts the query to:
... where e.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.
We strongly encourage you 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. You cannot specify a value for an undeclared variable with a .let statement. Also, unless the variable has been declared, attempting to pass a parameter with a null value to Report-Writer can produce incorrect results.
You can specify as many variables as needed 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.
You can specify variables anywhere in the query—for example, within the column list for a retrieve statement or as a value to which the contents of a column is compared. For example, the following query phrases are legal:
... where e.name = "$employee_name" ...
... retrieve (e.$var, ...) ...
When 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 (double quotes for QUEL), as shown in the preceding example.
If you actually want to include the dollar sign ($) as a literal part of the query, precede it with a backslash (\). For example:
... where e.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 Population Example in the appendix "Report-Writer Report Examples."