.Query Statement for QUEL Users
This statement specifies a QUEL query to be used to generate data for a report.
The .query statement for QUEL has the following format:
.query|.quer
{range_statement(s)}
retrieve [unique] (target_list) [where qual]
[sort by | order by...] sort-list
The parameters for the QUEL .query statement are as follows:
range_statement(s)
Valid QUEL range statements used to identify the tables used in the query.
target_list
A valid QUEL target list that creates data for the report. The retrieve into form of the retrieve statement is not used.
qual
A valid QUEL qualification to a query.
sort_list
A valid set of sort columns according to the rules for constructing QUEL retrieve statements. Note that you cannot use both a .sort statement and a sort by or order by clause in the same report specification.
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 1928 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
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 and Linux), 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.”
Examples
1. You set up the query as:
.query
range of e is emp
retrieve (e.all)
where e.salary > $sal
and e.dept = "$dept"
You invoke the command:
report mydb myrep (sal = 50000, dept = 'toy')
Enclose toy within single quotes to identify it to Report-Writer as a string. Report-Writer strips off these quotes before passing it to the query.
Note: If your operating system requires it, enclose the parenthetical clause within double quotes:
report mydb myrep "(sal = 50000, dept = 'toy')"
Report-Writer executes the following query:
range of e is emp
retrieve (e.all) where e.salary > 50000
and e.dept = "toy"
2. Consider a table called, account, with columns including custno, custname, checking, and savings. You have separate fields for checking and savings accounts on one row, because most customers have both a savings and a checking account with the bank. To write one report specification that prints either the savings or checking account balances with a single query, you can code a .query statement similar to the following:
.declare account_type = c10 with prompt
"Please enter the type of account:"
.quel
range of a is account
retrieve(a.custno, a.custname, val=a.$account_type)
You can invoke the query with the command:
report otherdb repname
At execution, Report-Writer issues the following prompt:
Please enter the type of account:
Suppose you respond with:
savings
The following query would be executed:
range of a is account
retrieve(a.custno, a.custname, val=a.savings)
To print the value of the savings column, use:
.println val
This query selects values from the database savings column, not the string constant, savings.