12. Report-Writer Statements : Report Setup Statements : .Query Statement—Indicate Start of a Query : Description
 
Share this page                  
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 2008 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) 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 (').