.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 in response to a runtime prompt, which you define in the with prompt 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.

.declare variablename = datatype
   [with null | not null]
    [with prompt 'promptstring']
    [with value 'valuestring']
   {, variablename = datatype...}

A valid variable name. In standard Ingres databases, the variable name can be up to 32 bytes long and must begin with an alphabetic, or underscore (_) character. Following characters must be alphanumeric or underscore. Also see Object Naming Conventions for ANSI/ISO Entry SQL-92 Compliant Databases.

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:

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.

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.

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.

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.