Mapping column data

In the following discussion, the mapping of data types from source to destination is grouped according to the destination data type:

Character destination

Source Destination Mapping
Character Character Data is left-justified and truncated or padded with spaces on the right, as needed.
Numeric Character Data is right-justified with leading zeros, as needed. Leading spaces are used, as needed, for floats. Destination column must be large enough to hold significant portion of the source numeric value; otherwise error results.
Binary Character Data is left-justified and truncated or padded with spaces on the right, as needed. There is no conversion.
Note: You cannot map a binary source column to a multi-byte or Unicode destination.
Date/Time Character Fixed date/time format applies, based on source column.
Note:
  • A character data type must have a minimum length of five, CHAR(5), to map floating point data types.
  • If you map a source date/time column to a destination character column, you can specify a different date format using the Age Function.
  • The following fixed formats apply when you map a date/time column to a character column:
    Date/Time
    Sybase ASE
    YYYY/MM/DD-HH:MM:SS.FFF
    SQL Server
    YYYY/MM/DD-HH:MM:SS.FFF
    Informix
    YYYY-MM-DD HH:MM:SS.FFFFFF (based on high/low qualifier)
    Small Date/Time
    Sybase ASE
    YYYY/MM/DD-HH:MM:SS
    SQL Server
    YYYY/MM/DD-HH:MM:SS
    Date
    DB2
    YYYY/MM/DD
    Oracle
    YYYY/MM/DD-HH:MM:SS
    Time
    DB2
    HH:MM:SS
    Timestamp
    DB2
    YYYY/MM/DD-HH:MM:SS.FFFFFF
    Oracle
    YYYY/MM/DD-HH:MM:SS.FFFFFFFFF
    Timestamp w/Time Zone
    Oracle
    YYYY/MM/DD-HH:MM:SS.FFFFFFFFF +/-HH:MM
    Timestamp w/Local Time Zone
    Oracle
    YYYY/MM/DD-HH:MM:SS.FFFFFFFFF
    Day/Time Interval
    Informix
    [-]DDDDDDDDD HH:MM:SS.FFFFF
    Day/Second Interval
    Oracle
    [-]DDDDDDDDD HH:MM:SS.FFFFFFFFF
    Year/Month Interval
    Informix
    [-]YYYYYYYYY-MM
    Oracle
    [-]YYYYYYYYY-MM
Note:
  • FFF equals hundredths of a second, FFFFFF equals millionths of a second, and FFFFFFFFF equals billionths of a second. Note that there will only be as many fractional seconds (F) decimal places as the specified scale.
  • For Timestamp with Time Zone columns, +/-HH:MM represents a time zone value, where HH is between -12 and +13.
  • For interval columns, [-] represents an optional negative sign. Also, note that the maximum digits are shown; however, there will only be as many day (D) digits or year (Y) digits as the specified precision.
  • Valid date values range from 1 to 9999.

Numeric destination

Source Destination Mapping
Character Numeric Character data must be valid as numeric data. Destination column must be large enough to hold source value; otherwise, error results. Decimal places can be dropped.
Numeric Numeric Destination column must be large enough to hold significant portion of the source value; otherwise, error results. Truncate decimals, if needed.
Binary Numeric Binary data is transformed to numeric. Destination column must be large enough to hold source value; otherwise, error results. Decimal places can be dropped.
Note:
  • If a destination column is defined as numeric, you cannot map a source column defined as date/time. Use the Age Function.
  • If the process of mapping significant positions of numeric data results in truncating the data, the data is not mapped and an error is reported.

Binary destination

Source Destination Mapping
Character Binary Data is left-justified and truncated or padded with NULL on the right, as needed. There is no conversion.
Note: You cannot map a multi-byte or Unicode source column to a binary destination.
Numeric Binary Destination column must be large enough to hold significant portion of source value; otherwise, error results. Data is right‑justified with leading zeros for integers and decimals. Leading spaces are used as needed.
Binary Binary Data is left-justified and truncated or padded with NULL on the right, as needed.
Note: If a destination column is defined as binary, you cannot map a source column defined as date/time, nor use the AGE function.

Boolean destination

Source Destination Mapping
Character Boolean Data is mapped from the first character. If the first character is 'T' , 'Y' , or ‘1', translate to TRUE. If the first character is 'F' , 'N', or ‘0', translate to FALSE.
Numeric Boolean A zero numeric value translates to FALSE. A non-zero numeric value translates to TRUE.
Binary Boolean Data is mapped from the first character. If the first character is 'T' , 'Y' , or ‘1', translate to TRUE. If the first character is 'F' , 'N', or ‘0', translate to FALSE.
Boolean Boolean Equal
Note: If a destination column is defined as Boolean, you cannot map a source column defined as date/time nor use the AGE function.

Date/Time destination

Source Destination Mapping
Date Date Equal
Time Date Not Supported
Timestamp Date Use date portion only.
Timestamp w/Time Zone Date Use date portion only; drop time zone.
Timestamp w/Local Time Zone Date Use date portion only.
Date Time Not Supported
Time Time Equal
Timestamp Time Use time portion only.
Timestamp w/Time Zone Time Use time portion only; drop time zone.
Timestamp w/Local Time Zone Time Use time portion only.
Date Timestamp Use source date and midnight.
Time Timestamp Use source time and current date.
Timestamp Timestamp Equal
Timestamp w/Time Zone Timestamp Use timestamp portion; drop time zone.
Timestamp w/Local Time Zone Timestamp Equal
Date Timestamp
w/Time Zone		 Use source date, midnight, and “+0”.
Time Timestamp
w/Time Zone		 Use source time, current date, and “+0”.
Timestamp Timestamp
w/Time Zone		 Use timestamp and “+0”.
Timestamp
w/Time Zone		 Timestamp
w/Time Zone		 Equal
Timestamp
w/Local Time Zone		 Timestamp
w/Time Zone		 Use timestamp with “+0”.
Date Timestamp
w/Local Time Zone		 Use source date and midnight.
Time Timestamp
w/Local Time Zone		 Use source time and current date.
Timestamp Timestamp
w/Local Time Zone		 Equal
Timestamp
w/Time Zone		 Timestamp
w/Local Time Zone		 Use timestamp portion; drop time zone portion.
Timestamp
w/Local Time Zone		 Timestamp
w/Local Time Zone		 Equal
Note:
  1. If a destination column is defined as date, time, or timestamp, you can specify a date/time special register as the source.
  2. If a destination column is defined as date/time, you cannot map a source column defined as character or numeric. Use the Age Function.
  3. If a destination column is defined as date/time, you cannot map a source column defined as binary or Boolean. Use the Age Function.
  4. If the destination column is a Sybase ASE/SQLServer DateTime or SmallDateTime column, AND the source column is NOT a Sybase ASE/SQLServer DateTime or SmallDateTime column, AND the hour value is greater than or equal to 24, AND the SybTimeOverflow option is TRUE, the destination time value is set to 00:00:00.000000.
  5. If the source or destination column is an Oracle Timestamp with Time Zone or Timestamp with Local Time Zone column, mapping to a column of a different data type may produce unexpected results due to Oracle assumptions for storing and retrieving this data.

    For example, for a Timestamp with Local Time Zone column, Oracle normalizes the time stored in the database to the session time of the Oracle server. When Oracle retrieves this data, the stored value is adjusted by the difference between the server session time and the client session time. However, Optim™ returns the value stored in the database without making adjustments.

    Therefore, it is recommended that you map Timestamp with Time Zone columns and Timestamp with Local Time Zone columns to columns of the same date type only.

Special Registers

The following are various special registers or functions for putting the current date and/or time into a Destination column. They can be used interchangeably.

  • CURRENT DATE
  • CURRENT_DATE
  • CURRENT TIME
  • CURRENT_TIME
  • CURRENT TIMESTAMP
  • CURRENT_TIMESTAMP
  • CURDATE
  • CURTIME( )
  • GETDATE
  • GETTIME( )
  • NOW( )
  • SYSDATE

The following are special registers for putting a User ID into a Destination column. Note that they do not provide the same value.

CURRENT_SQLID
User ID as known to database
USER
User ID as known to database
WORKSTATION_ID
User ID as known to Windows 95/NT
Note: Mapping special registers is based on the length of the destination column.