Additional Columns

Oracle supports only some Lawson database features. To make these features work with the Oracle server, the database driver adds columns to the rows that make up the data tables in the database.

Subsets

Lawson databases use subsets. A subset is an index that has a condition or conditions attached to it. A subset index finds only those rows that satisfy the condition.

To make subsets work in Oracle, the database driver does two things. First, it adds a subset switch column to tables with subsets (that is, indexes that have conditions). Second, it makes sure the index allows duplicates. The database driver creates one subset switch column of type CHAR (01) for each index with a condition. If a row satisfies the condition, the database driver puts a Y in this column.

All rows that satisfy this condition are unique. If a row does not satisfy the condition, the database driver puts N in the column. The rows that do not satisfy the condition might be duplicates. When a condition exists, the subset column is the first column in the Oracle index. The name of this additional column is the index name followed by _SS_SW (SubSet SWitch).

For example, if the Lawson GLMASTER table has an index GLMSET4 with a condition, the database driver adds a column named GLMSET4_SS_SW to the GLMASTER table in Oracle. This column has Y and N values identifying the rows that do and do not satisfy the condition, respectively.

The subset switch columns attach to the table immediately after the last normal column. If there are multiple subset indexes, the subset columns attach to the table in index-number order. These columns must have Y and N values in them for the database driver to process the rows correctly. Therefore, update the values of these columns every time you update the row with a non-Lawson program.

You also must assign the correct values to the columns when you create the row using a non-Lawson program. Use Oracle triggers to automatically set the correct value.

Date Types

The use of YYMMDD fields (6-digit dates) in indexes is not supported. If a date field needs to be indexed, use an 8-digit YYYYMMDD field. If you use a YYMMDD field in an index, dates may not be returned in the expected order.

Occurring Columns

Lawson database design allows occurring columns (one-dimensional arrays). Oracle does not handle such columns directly, but the database driver can store occurring columns in Oracle by breaking each occurrence of the array into a separate column.

When you store each occurrence of a column in a separate column, each occurrence stores with the correct data type. The name of each occurrence is the column name followed by _n , where n is the number of the occurrence.

For example, if the occurring column name is MONTH-PTS, the first occurrence in Oracle is MONTH_PTS_01, the second is MONTH_PTS_02, and so on.

Groups

The database driver treats occurring groups like occurring columns in Oracle. For example, a table in the Lawson dictionary has an occurring group as follows.

Field Name     Occurs    Type         Size
----------     ------    ----         ----
Date-Group     5         GROUP
Begin-Date               ccyymmdd     8
End-Date                 ccyymmdd      8

The database driver changes the table information to the following format.

BEGIN_DATE_GROUP       CHAR(40)
END_DATE_GRP          CHAR(40)

The database driver stores data in these columns as if they were occurring columns. Every option that applies to occurring columns also applies to occurring groups. Only a logical connection between the columns preserves the group concept.

Column Order

The database driver creates columns in the order in which they are defined. The database driver puts:

  • Long strings (_0, _1, and so on) and occurring columns (_n) in order of their definition.

  • Subset switch columns (_SS_SW) immediately after the last normal column.

Database Field Type Mapping

Data type Oracle designation
Binary Document

BLOB
Binary Object

BLOB
Text

CLOB
TimeStamp

TIMESTAMP(2)
Year

NUMBER(4)
Boolean

CHAR