Setting Up Your System › Setting Up Database Access › Setting Up SQL Access to Non-SQL Databases › Fixed OCCURS Element Definitions

Fixed OCCURS Element Definitions

Although OCCURS…DEPENDING ON declarations are not available for SQL access, fixed OCCURS definitions are available. To the SQL user, a fixed OCCURS element appears as one column for each occurrence of the element. The column name for each occurrence is the original element name followed by an underscore and an occurrence number. If the element is declared with multiple OCCURS levels, the corresponding column names contain one underscore and one occurrence number for each dimension of the OCCURS declaration.

For example, the element definition BUD-AMT OCCURS 12 TIMES generates the following column names:

BUD_AMT_01, BUD_AMT_02, BUD_AMT_03...BUD_AMT_12.

Note: The occurrence number attached to the column name must be large enough to accommodate the largest subscript from the corresponding element definition.

The base element name, combined with the appended occurrence information, cannot have more than 32 characters. If it does, you must define an SQL synonym for the non-SQL record.

Although the CA IDMS SQL implementation allows 32-character column names, other SQL implementations restrict column names to 18 characters. Some ODBC client software, in particular, may require SQL synonyms for non‑SQL records to limit the size of the transformed column names to 18 characters.

Note: Another way to define shorter names is to create a view of the record and specify view column names.