|
SQL Employee Columns Required
|   |
|
SQL Employee Columns Required
| |
Note that you MUST define each DMS SQL column in your script; you cannot leave a column out even if you are not using the column. Each column must have a valid value of the type required. Null values are not permitted.
The SQL Server import columns and the Oracle importing columns are similar in layout, but the variable types are different so they have been separated into two sections:
SQL Server Employee Columns Required
If you are not using a column, set that column value to 0 for integer or float, blank for VarChar, false for Boolean values.
Unless otherwise stated, the following are the range of values:
|
Big integer (INT64) |
An integer number. Internal IDs assigned by SQL can range from 1 to 9 quintillion (9,223,372,036,854,775,807). For quantities, number can be from 1 to 9,999,999,999. |
|
Boolean |
True or False |
|
Date |
A valid date. For blank dates use Convert(Date,'',0) to create the date of 01/01/1900 and the import will take any date less than the year 2000 and set the date to blank. You can also use Cast to cast a value to a date. |
|
Defaults |
Defaults for items are set within DMS using “Terms & Settings” under the menu category “Tables”. |
|
Float |
7 significant digits (i.e. 99999.99) |
|
Integer |
0 to 2 billion (2,147,483,647) |
|
Text |
A Varchar with a maximum length of 1 GB (about 1 billion characters). Practically speaking, a Text field can hold several paragraphs of text. For blank notes, use Convert(Text,TextToCast,0) as Notes You can also use Cast(TextToCast as Text) as Notes |
|
Varchar x |
Where x is the maximum number of alphanumeric characters in the Varchar |
During the import, if an employee number already exists in the table, the employee is updated with the new imported data. Duplicate employee numbers are not allowed.
|
|
Field |
Type |
Comment |
|
|
EmployeeNumber |
Varchar(20) |
Required unique field. Employee number of the employee record to add or update. |
|
|
EmployeeName |
Varchar(50) |
Employee name. The name any format such as "Last Name, First name" or "First Name Last Name", etc. |
|
|
Available |
Boolean |
True if the employee is available for use. False if the employee is not available (i.e. left the company). |
|
|
Notes |
Memo |
Optional text about the employee. |
Oracle Employee Columns Required
If you are not using a column, set that column value to 0 for integer or float, blank for VarChar2, or 0 (false) for Boolean values.
Unless otherwise stated, the following are the range of values:
|
Number(10) |
A large integer number; Actual format in Oracle is Number(10,0) |
|
Boolean |
Number(1); 0 for false or 1 for true; Actual format in Oracle is number(1,0) |
|
Date |
Valid date |
|
Float |
7 significant digits (i.e. 99999.99) |
|
Integer |
Integer values range from Number (2, 0) up to Number (5,0) |
|
VarChar2 (x) |
Where x is the maximum number of alphanumeric characters in the VarChar2 |
If you have information stored in a CLOB (Character Large OBect) in a column in the database, the following Oracle statement can be used to convert it into a VarChar2 for DMS:
dbms_lob.substr(NotesInCLOB, 2000, 1) as Notes
In the example above, NotesInCLOB is the column name in the Oracle database and Notes is the DMS required column that needs to be populated.
During the import, if an employee number already exists in the table, the employee is updated with the new imported data. Duplicate employee numbers are not allowed.
|
|
Field |
Type |
Comment |
|
|
EmployeeNumber |
Varchar2(20) |
Required unique field. Employee number of the employee record to add or update. |
|
|
EmployeeName |
Varchar2(50) |
Employee name. The name any format such as "Last Name, First name" or "First Name Last Name", etc. |
|
|
Available |
Boolean: Number(1) |
1 (true) if the employee is available for use. 0 (false) if the employee is not available (i.e. left the company). |
|
|
Notes |
Varchar2(2000) |
Optional text about the employee. |