Splitting an Unsigned Number Item into Two Items

If you have NUMBER(12) items whose first two digits represent an account type and the remaining ten digits represent the account number, you might want to split this item into two columns. You can then rename the two columns as described in Renaming Columns.

In the following scripts, the NUMBER(12) item is named L_APPL_ACCT and is part of the data set LOAN. This item is mapped into two columns, the first of which contains 2 digits while the second one contains 10 digits. When the Client splits an item it appends “_x1” and “_x2” to the column names it creates to avoid having to deal with duplicate names.

File name: script.user_layout.loan

update DMS_ITEMS set di_options = 1048576, dms_subtype = 2

where dms_item_name = ‘L-APPL-ACCT’ and dataset_name = ‘LOAN’

For SQL Server, this results in columns l_appl_acct_x1 (data type tinyint) and l_appl_acct_x2 (data type bigint).

You can also make the client convert the first column to CHAR by setting bit 1024 in di_options to force the data to be stored using a data type of CHAR(2) in the relational database.

File name: script.user_layout.loan

update DMS_ITEMS set di_options = 1049600, dms_subtype = 2

where dms_item_name = ‘L-APPL-ACCT’ and dataset_name = ‘LOAN’