Rules and Guidelines for Functions in SQL ELT Optimization
Rules and Guidelines for Functions in
SQL ELT Optimization
Use the following rules and guidelines when pushing functions to a Microsoft Azure SQL Data Warehouse database:
Upsert operations for
SQL ELT optimization
are not applicable when you use an ODBC connection.
The
datetimeoffset
datatype is applicable only in passthrough mappings.
The Microsoft Azure SQL Data Warehouse aggregate functions accept only one argument, which is a field set for the aggregate function. The agent ignores any filter condition defined in the argument. In addition, ensure that all fields mapped to the target are listed in the
GROUP BY
clause.
To push the
TO_CHAR()
function to the Microsoft Azure SQL Data Warehouse database, you must define the date and format arguments.
When you push the
SYSTIMESTAMP()
and
SYSDATE()
functions to the Microsoft Azure SQL Data Warehouse database, do not specify any format. The Microsoft Azure SQL Data Warehouse database returns the complete time stamp.
SYSDATE
works without brackets
()
only, if used it shows as invalid expression.
You cannot push the
TO_BIGINT()
or
TO_INTEGER()
function with more than one argument to the Microsoft Azure SQL Data Warehouse database.
When you push the
REPLACECHR()
or
REPLACESTR()
function to the Microsoft Azure SQL Data Warehouse database, the agent ignores the caseFlag argument.
For example, both
REPLACECHR(false, in_F_CHAR, 'a', 'b')
and
REPLACECHR(true, in_F_CHAR, 'a', 'b')
return the same value.
To push
INSTR()
to Microsoft Azure SQL Data Warehouse database, you must only define string, search_value, and start arguments. Microsoft Azure SQL Data Warehouse does not support occurrence and comparison_type arguments.
Microsoft Azure SQL Data Warehouse connector supports the following date formats with the
TO_DATE()
function:
YYYY-MM-DD HH24:MI:SS.NS
YYYY-MM-DD HH12:MI:SS.NSAM
MON DD YYYY HH12:MI:SS.NSAM
MON DD YYYY HH24:MI:SS.NS
DD MON YYYY HH12:MI:SS.NSAM
DD MON YYYY HH24:MI:SS.NS
MM/DD/YY HH12:MI:SS.NSAM
MM/DD/YY HH24:MI:SS.NS
MM/DD/YYYY HH12:MI:SS.NSAM
MM/DD/YYYY HH24:MI:SS.NS
HH24:MI:SS.NS
HH12:MI:SS.NSAM
To push the
SET_DATE_PART()
function to the Microsoft Azure SQL Data Warehouse database, you must use the following date data types as arguments:
datetime
datetimeoffset
datetime2
smalldatetime
You can use the following formats for date data types:
YYYY, YY, YY, Y
MM, MON, MONTH
D, DD, DD, DY, DAY
HH, HH12, HH24
MI
MS
SS
NS
and
US
formats are not applicable to
SET_DATE_PART()
.
To push the
ADD_TO_DATE()
function to the Microsoft Azure SQL Data Warehouse database, you must use the following date data types as arguments:
date
datetime
datetimeoffset
datetime2
smalldatetime
time
You can use the following formats for date data types:
YYYY, YY, YY, Y
MM, MON, MONTH
D, DD, DD, DY, DAY
HH, HH12, HH24
MI
MS
SS
NS
: applicable to
datetimeoffset
,
datetime2
, and
time
US
To push the
MAKE_DATE_TIME()
function to the Microsoft Azure SQL Data Warehouse database, you must use the following date data types as arguments:
date
datetime
datetimeoffset
datetime2
smalldatetime
time
You can use year, month, day, hour, minute, second, and nanosecond with appropriate return date types.
ASK INFAPreview
