Preface
Function reference
- Function components
Constants
Operators
Dates
Functions
- Function overview
  - Aggregate functions
    - Filter conditions
  - Conversion functions
  - Data cleansing functions
  - Date functions
  - Encoding functions
  - Financial functions
  - Horizontal expansion functions
  - Numeric functions
  - Scientific functions
  - Special functions
  - String functions
  - Test functions
  - Window functions
    - Aggregate functions as window functions
    - Nested aggregate functions as window functions
- Function quick reference
- Functions in advanced mode
- %OPR_CONCAT%
- %OPR_CONCATDELIM%
- %OPR_IIF%
- %OPR_SUM%
- ABORT
- ABS
- ADD_TO_DATE
- AES_DECRYPT
- AES_ENCRYPT
- AES_GCM_DECRYPT
- AES_GCM_ENCRYPT
- ASCII
- AVG
- CEIL
- CHOOSE
- CHR
- CHRCODE
- COMPRESS
- CONCAT
- CONVERT_BASE
- COS
- COSH
- COUNT
- CRC32
- CUME
- DATE_COMPARE
- DATE_DIFF
- DEC_BASE64
- DECODE
- DECOMPRESS
- ENC_BASE64
- ERROR
- EXP
- FIRST
- FLOOR
- FV
- GET_DATE_PART
- GREATEST
- IIF
- IN
- INDEXOF
- INITCAP
- INSTR
- IS_DATE
- IS_NUMBER
- IS_SPACES
- ISNULL
- LAG
- LAST
- LAST_DAY
- LEAD
- LEAST
- LENGTH
- LN
- LOG
- LOWER
- LPAD
- LTRIM
- MAKE_DATE_TIME
- MAX (Dates)
- MAX (Numbers)
- MAX (String)
- MD5
- MEDIAN
- METAPHONE
- MIN (Dates)
- MIN (Numbers)
- MIN (String)
- MOD
- MOVINGAVG
- MOVINGSUM
- NPER
- PERCENTILE
- PMT
- POWER
- PV
- RAND
- RATE
- REG_EXTRACT
- REG_MATCH
- REG_REPLACE
- REPLACECHR
- REPLACESTR
- REVERSE
- ROUND (Dates)
- ROUND (Numbers)
- RPAD
- RTRIM
- SET_DATE_PART
- SETCOUNTVARIABLE
- SETMAXVARIABLE
- SETMINVARIABLE
- SETVARIABLE
- SHA256
- SIGN
- SIN
- SINH
- SOUNDEX
- SQRT
- STDDEV
- SUBSTR
- SUM
- SYSTIMESTAMP
- TAN
- TANH
- TO_BIGINT
- TO_CHAR (Dates)
- TO_CHAR (Numbers)
- TO_DATE
- TO_DECIMAL
- TO_FLOAT
- TO_INTEGER
- TRUNC (Dates)
- TRUNC (Numbers)
- UPPER
- VARIANCE
System variables
Datatype reference

Function Reference

Back Next

Rules and guidelines for datatypes

Use the following rules and guidelines for datatypes and conversion:

The default datatype for all fields in flat files is Nstring(255).

If you need to change a datatype, use the

Edit Metadata

option to edit the native type or precision for the field.

The task may have unexpected results if you map incompatible datatypes between source and target fields or between the output of an expression and a target field.

For example, if you map a datetime column of a MySQL database source to an integer column of a Salesforce target in a

synchronization

task, the task fails.

Numeric data with a precision greater than 28 is truncated to a precision of 15 when written to a target.

For example, 4567823451237864532451.12345678 might be written to the target as 4567823451237864000000.00000000.

synchronization

task fails when you map source fields of the following datatype and database type to a Salesforce target:

Datatype	Database type
tinyint	MySQL
tinyint	SQL Server
interval year to month	Oracle
interval day to second	Oracle
rowid	Oracle
urowid	Oracle

In most cases, the

replication

task creates the same precision and scale in the target as the source. In other cases, the

replication

task uses a different precision or scale:

In some cases, the

replication

task does not specify the precision or scale, and the database uses the defaults. For example, the source is MySQL and source datatype is Double(12). The

replication

task creates the Number datatype in an Oracle target and does not specify the precision.

If the source is not Oracle, the target is Oracle, and the source datatype is nchar, nvarchar, or nclob, the application multiples the source field precision by 2, up to a maximum of 64000, to obtain the target field precision.

If the source is MySQL, the target is Microsoft SQL Server 2000 or 2005, and the source datatype is date or time, the target field datatype is Timestamp(23, 3).

If a

replication

task writes data from MySQL to a flat file and the source contains time data,

Data Integration

converts the time data to a date/time datatype, where the date is the current date and the time is the time specified in the source.

You can use a string function in an expression to remove the date before loading the flat file.

For an Oracle source or target, the precision of a Number field must be greater than or equal to the scale. Otherwise, the task fails.

When a

synchronization

task writes 17-digit or 18-digit numeric data with no scale from Salesforce to an Oracle column with a Number datatype, the task may produce unexpected output in the target.

For example, the

synchronization

task writes the Salesforce value 67890123456789045 as 67890123456789048 in an Oracle target.

When you use an ODBC connection for an Oracle database target, ensure that the maximum precision for an Oracle table column does not exceed the following values: char(1999), varchar(3999), nvarchar(3998), and nchar(3998).

A task may load corrupt data into the target if the data comes from a source field of the Real datatype from Microsoft SQL Server.

Datatype reference

Download Guide

Watch

Comments

Cloud Data Integration Homepage