.Query Statement--Indicate Start of a Query
The .query statement specifies an SQL or QUEL query to be used to generate data for a report. The statement syntax and description provided here apply to SQL only. If you are using the QUEL language, see
.Query Statement for QUEL Users.
The .query statement has the following format:
.query | .quer
select [all | distinct] column_list
[as resultcolumn_list]
from [schema.]table |view|synonym[corr_name]
{, table |view|synonym[corr_name]}
[where search_condition]
[group by column {, column}]
[having search_condition]
{union select_statement }
[order by ...]
For a complete explanation of the syntax and parameters for the SQL select statement within the Report-Writer .query statement, see the SQL Reference Guide.
Description
The .query statement indicates the start of a valid SQL query that creates the data to be reported. This query follows the same rules as any other SQL select statement, although it can also contain variables. You can use as many lines as you need to specify the query. The end of the query is indicated by the start of a new report formatting statement.
Specify either the .query or the .data statement (but not both) for every report. Only one .query statement is permitted for a report, and only one data retrieval statement is permitted within the .query statement. Both a .query with an order by clause and a .sort statement cannot be in the same report specification.
You can use the optional select column_list as resultcolumn_list construct to reference a column in subsequent statements by a name other than its database table column name. For example, if you selected A_column as B_column, thereafter you need to refer to B_column in all statements referencing that column. This construct is useful for printing the value of a column that is specified as a runtime variable. When Report-Writer encounters a .print statement that directly references a runtime variable for a column name (for example, .print $account_type), it prints the name of the column as entered at runtime, rather than its data. To print the data rather than the column name, you use the select column_list as resultcolumn_list construct in the query and reference the resultcolumn_list name in the .print statement.
For example:
.query
select $account_type as val
.print val
Because the .query statement generates a standard query, the standard limits apply to any report's query. For Ingres databases, these limits are 1024 columns and 1928 bytes per row. These limits are extended for some Enterprise Access products. For details, see your Enterprise Access product guide.
If any column you specify with select * 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 select 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 specify the table, view, or synonym as
schema.objectname. You can also use delimited identifiers for table, column, or schema names if you have previously specified the .delimid statement. This includes use of
delimited identifiers (see
Delimited Identifiers on page 47) as either or both the correlation name and the column name in a
correlationname.identifier as
resultcolumn construct. For more information, see the .delimid statement.
To avoid any potential confusion with delimited identifiers, which are double‑quoted, enclose string constants with the standard SQL string delimiter, the single quote (').
Variables
For standard Ingres databases, variable names:
• Must begin with a letter
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 on page 240.
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.”
Examples
1. You set up the query as:
.query
select *
from emp
where salary > $sal
and 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:
select *
from emp
where salary > 50000
and dept = 'toy'
2. Consider a table called account owned by davis with columns including customer number, customer name, 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.
If you want to write one report specification that prints either the savings or checking account balances with a single query, you could code a .query statement similar to the following, using the select column_list as resultcolumn_list construct:
.delimid
.declare account_type = c10 with prompt
'Please enter the type of account:'
.query
select "customer number", "customer name",
$account_type as val
from davis."bank accounts"
The column names and table name in the preceding code must be double‑quoted, according to standard rules for specifying delimited identifiers.
You can invoke the preceding query with the command:
report otherdb repname
At execution, Report-Writer issues the following prompt and, in this example, the user enters the column name, savings:
Please enter the type of account: savings
The following query would be executed, which selects values from the savings column and assigns them to the result column name, val:
select "customer number", "customer name", savings as val
from davis."bank account"
To print the value of the column, savings, refer to the result column name, val:
.println val