.Declare Statement--Declare Variables
The .declare statement declares variables that can be assigned values and used in expressions. More than one .declare statement can be specified in a report.
The .declare statement declares variables that can be assigned values interactively or in Report-Writer code. You can assign a value to the variable in any of the following ways:
• On the command line during runtime
• On the command line in response to a runtime prompt, which you define in the with prompt clause of a .declare statement
• In .let assignment statements placed in any .header, .footer, or .detail sections.
• In the with value clause of a .declare statement
Declared variables can also be used in a query block to specify runtime substitution of text in the query. For details, see the .Query statement.
The .declare statement has the following format:
.declare variablename = datatype
[with null | not null]
[with prompt 'promptstring']
[with value 'valuestring']
{, variablename = datatype...}
The parameters for the .declare statement are as follows:
variablename
Note: To reference the value of a declared variable within a report specification, the variable must be preceded with a dollar sign ($); otherwise, the name of the variable is referenced. Do not use the dollar sign ($) when referencing the name of the variable in the .declare statement or on the left side of an assignment in the .let statement. For example:
.declare var = c3 . . .
.let var = 'abc'
.print $var
datatype
A legal data type.
The .declare statement declares each variable to be the given data type. You can specify whether a data type is nullable or not nullable by including the with null or not null option.
• If the variable is declared as nullable with the with null option, it is automatically initialized to the null value.
• If the variable is declared with the not null option, it is initialized to the default value for the data type.
If you specify neither option, the variable data type defaults to null or not null, depending on the query language (SQL or QUEL) used in the .query statement. If you specify a .data statement instead of the .query statement, the installation default language determines default nullability.
promptstring
A string constant up to 100 characters in length including quotes. The constant must be enclosed in single quotes to preserve spaces. For more information, see
String Constants.
The with prompt option instructs Report-Writer to prompt for the initial value of the variable at runtime, using the specified prompt string. The with value option instructs Report-Writer to use an initial value that you specify for the variable in the .declare statement. Using both the with prompt and with value options allows you to specify a default value if the user fails to enter a value at the prompt. This value remains unchanged by any .let statement until after the query. For more information, see the .query and .let statements.
valuestring
An initial string value for the variable up to 100 characters in length including quotes. The value must be expressed as a constant in character, numeric, or date format and must be enclosed in quotes to preserve spaces. It can contain delimited identifiers (enclosed in double quotes) or schema.objectname constructs.
You can use the schema.objectname construct in the with value clause, as appropriate. You can also use a delimited identifier as the schema name, table name, or name of a column in the with value clause, if you have previously specified the .delimid statement.
For example, the following code fragment declares the variable, default_table, with an initial default value of a table belonging to the schema “dave” and whose name is the delimited identifier, “my table.” The value string parameter for the with value clause is enclosed in single quotes.
.declare default_table=varchar(65) with value
'dave."my table"'
.query
select * from $default_table
If you do not specify an initial value or prompt, and you reference the variable outside of a query block, the initial value is null (or the default value for that data type, if not null was used). When you reference a declared variable within a query block, its initial value must be entered either in the .declare statement, on the command line, or in response to a prompt string specified in the .declare statement.
Note: Some statements (such as .query) accept variables that can be executed before the .header report statement. However, variable values cannot be assigned with the .let statement before the .header report statement. In these cases, the person running the report must specify the value of a variable as a parameter on the command line or in response to a prompt.
The with value option, used in conjunction with the .include statement, allows greater reporting flexibility. For example, you can create various include files to define date formats for various languages. Instead of entering the initial value of the date format interactively, the date formats are initialized in the .declare statement during the loading of the report specification. For more information, see Example 2 in the following section, Examples.
If both the with prompt and with value options are included in a declaration, the promptstring overrides the valuestring. No warning is issued about the override. The user is prompted with the specified promptstring for an initial value at runtime.
Examples
1. Declare variables using with prompt, with null, and not null.
.declare
counter = integer,
salary = money with prompt
'Please enter the salary:',
spouse = c30 with null,
dept = i4 not null with prompt
'What department?'
2. Declare variables using with value in .include files “BR_fmts.rw” and “AM_fmts.rw,” and then include the appropriate definition for that version of your report.
.declare date_fmt = c30 with value 'd\'03/20/01\''
or
.declare date_fmt = c30 with value 'd\'02/03/01\''
The main body of the report can be written independently of the exact value of the date format; for example:
.print current_date ($date_fmt)
3. Declare a variable with a value that is an schema‑qualified table name.
.declare var1 = varchar(65) with value 'mike.table_abcd'
The schema and table name can be delimited identifiers, if you have specified the .delimid statement. The variables have been declared with the maximum size to accommodate a compound identifier in which each part is a delimited identifier.
.delimid
.declare var2 = varchar(133)
with value 'jane."table efg"'
.declare var3 = varchar(133)
with value '"c bradley".table_hijk'
.declare var4 = varchar(133)
with value '"r panzer"."table xyz"'