Additional Columns

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 Microsoft SQL Server, 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 Microsoft SQL Server 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 Microsoft SQL Server. 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 Microsoft SQL Server triggers to automatically set the correct value.

Descending Index Columns

Microsoft SQL Server supports descending columns in indexes. A descending index column with the DESC attribute in the index is created by the Lawson database driver, where appropriate.

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). Microsoft SQL Server does not handle such columns directly, but the database driver can store occurring columns in Microsoft SQL Server 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 Microsoft SQL Server is MONTH_PTS_01, the second is MONTH_PTS_02, and so on.

Groups

Lawson applications no longer use occurring groups (two-dimensional arrays); however, some older Lawson applications might still have them. The database driver treats occurring groups like occurring columns in Microsoft SQL Server. 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 for Microsoft SQL Server.

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.