Numeric Date and Time in Non-Contiguous Columns

When a DMSII date and time are in contiguous column, you can easily make the Client handle the combined columns as a single date/time quantity by merging the two columns. You can do this by modifying the values of the dms_decl_length and dms_length columns for the first DMSII item to be the sum of the values for the two columns. You would then set active=0 in DATAITEMS for the second item. For example, if you have an item that is a NUMBER(8) representing a date which is immediately followed by an item that is NUMBER(6) representing a time, you can make the Client treat the first item as if it were a NUMBER(14) and disable the second item. This can also be done by using DBGenFormat.

If the two columns are not contiguous, you can use the dms_concat_num column to append the time part of the combined item to the date part. This column must be set to the item number of the item containing the time value. The Client will effectively treat these two items as if the second one were concatenated to the first one. This is a lot more efficient than using DBGenFormat to perform this operation.

An example of such a layout script follows.

script.user_layout.dttest:

update DMS_ITEMS
set dms_concat_num =(select dms_item_number from DMS_ITEMS

where dms_item_name='SALE-TIME' and dataset_name='DTTEST'),

di_options = 0x82,

dms_subtype = 111

where dms_item_name='SALE-DATE' and dataset_name ='DTTEST'

script_user_define.lc:

update DATAITEMS

set active=0

where item_name ='sale_time' and table_name ='dttest'

This combines the columns SALE-DATE and SALE_TIME into a column that effectively replaces SALE_TIME and is to be cloned as a long date with a date format of 111. The second script sets active 0 for the sale_time column so this value is not cloned twice. Setting the active column to 0 for SALE-DATE will not work because the information for the column would not appear in DATAITEMS, making it impossible to perform the concatenation.