SQL Concepts and Language Elements › Supported SQL Language Elements › Host Variables

Host Variables

A CA Ideal group, field, or parameter that is defined in a dataview, panel, working data, or parameter data, and that is used in embedded SQL.

You can prefix a host variable in embedded SQL with a colon; that is:

:host‑identifier

The identifier must immediately follow the leading colon.

For DB2: You can omit the colon from a host identifier when the host identifier is not a reserved word or when it meets any of the shown in the list that follows.

For CA Datacom/DB: You can omit the colon from a host identifier when each host identifier used in a statement meets the following conditions:

• It is qualified by a host structure (group) name that is not a reserved word.

  For example, you can specify the subsidiary field SUR_NAME, which is part of group LONG_NAME, as :SUR_NAME, as LONG_NAME.SUR_NAME, or as :LONG_NAME.SUR_NAME.

• It is referenced in a context where column names are illegal (that is, in an INTO or VALUE clause or in the LIKE or IN predicates) and it is not an SQL reserved word or PDL verb.

You can use an SQL column name defined in a CA Ideal dataview as a host variable, but only in the logical scope of a FOR statement executed for the dataview. See the FOR Statement for SQL in The chapter Built-In Functions for a description of embedded SQL and the FOR.

An alpha group is treated as an elementary alpha field in embedded SQL except when it is used in an INTO or VALUE clause with BY POSITION. In that case, each subfield in the group is treated as an elementary alpha field. You can use a non‑alpha group as a host variable only with BY POSITION. In that case, each subfield in the group is treated as an elementary field of the appropriate type.

You can use COPY DATAVIEW to automatically include an image of an SQL dataview in a working data or parameter data group item.

Host variables that correspond to SQL date and time type columns can be alphanumeric fields that follow the default formats (listed previously under data types) or that follow local date and time default formats specified in a DB2 user exit. You can also use CA Ideal date fields as host variables that correspond to SQL date type columns. You can use a CA Ideal date field anywhere an SQL date type column is acceptable, except as the target of a FETCH INTO when a local default date exit is in effect.