To push IS_DATE() function to Google BigQuery, you must configure the output field in the expression transformation to a column of string data type.

When you push the IS_DATE() function to Google BigQuery and use the SS.US format argument and specify values with the SS.MS or SS format, the IS_DATE() returns true.

When you push the IS_DATE() function to Google BigQuery and use the MON format argument and specify values with the MONTH format, the IS_DATE() returns true.

When you push the IS_DATE() function to Google BigQuery and use the MONTH format argument and specify values with the MON format, the IS_DATE() returns true.

When you use Is_Number(), Is_Spaces(), and Is_Date() in an Expression transformation, the output field type supports only integer and string data types.

When you push a function to Google BigQuery and the mapping runs without

SQL ELT optimization

, the IS_DATE() returns Boolean values of 0 or 1 to the Google BigQuery target table. If the mappings run with

SQL ELT optimization

, the IS_DATE() returns Boolean values of true or false to the Google BigQuery target table.

When you specify Is_Number(), Is_Spaces(), or Is_Date() functions in an Expression transformation, the output field type supports only integer and string data types.

To push the TO_CHAR(DATE) function to Google BigQuery, you must use the following string and format arguments:

YYYY
YY
RR
Q
MM
MON
MONTH
DD
DDD
DY
DAY
HH12
HH24
MI
SS
SS.MS
SS.US
am
AM
pm
PM

To push the TO_DATE(string, format) function to Google BigQuery, you must use the following format arguments:

YYYY
YY
RR
MM
MON
MONTH
DD
HH12
HH24
MI
SS
SS.MS
SS.US
am
AM
pm
PM

To push the ADD_TO_DATE(date, format, amount) or TRUNC(date, format) function to Google BigQuery, you must use the following format arguments:

YYYY
YY
YYY
Y
MM
MON
MONTH
D
DD
DDD
DY
DAY
HH
HH12
HH24
MI
SS
MS
US

To push the GET_DATE_PART(date, format) function to Google BigQuery, you must use the following format arguments:

YYYY
YY
YYY
Y
MM
MON
MONTH
DD
DDD
DY
DAY
HH
HH12
HH24
MI
SS
MS
US

When you push the GET_DATE_PART() function to the Google BigQuery database and specify null in the format argument, the mapping runs without

SQL ELT optimization

.

When you push the LAST_DAY() function to the Google BigQuery database and specify Date/Time or Timestamp value in the date argument, the LAST_DAY() function pushes only the date values to the Google BigQuery target. You might encounter a data mismatch when you compare a mapping that runs with full

SQL ELT optimization

and a mapping that runs without

SQL ELT optimization

.

To push the ROUND(DATE) function to Google BigQuery, you must use the following format arguments:

DD
DDD
DY
DAY
HH
HH12
HH24
MI
SS
MS

When you push the ROUND(DATE) function to the Google BigQuery database and use NS (nanoseconds) in the format argument, the mapping runs without

SQL ELT optimization

or with source

SQL ELT optimization

.

To push the ROUND(NUMBER) function to Google BigQuery, you must use a numeric value of the following data types:

Decimal
Numeric
NULL

To push the INSTR() function to Google BigQuery, you must only define the input_field and string arguments.

When you push the SYSTIMESTAMP() function to the Google BigQuery database, do not specify any format arguments. If you do not specify any format arguments, the Google BigQuery database returns the complete timestamp.

If you use a % operator in an expression transformation, the mapping converts the % operator to the MOD() function and pushes the MOD() function to Google BigQuery.

When you push the TRUNC(DATE) function to the Google BigQuery database and specify a NULL value in the format argument, the mapping runs without

SQL ELT optimization

.

When you push the SYSDATE() function to the Google BigQuery database and the mapping runs with

SQL ELT optimization

, the function returns the current date and time based on the time zone associated with the Google BigQuery database.

When you push the SUBSTR() function to the Google BigQuery database, you must specify a value of the String data type in the string argument. If you pass a numeric value, the mapping runs without

SQL ELT optimization

.

When you push the SUBSTR() function to the Google BigQuery database, the value of the length argument must be an integer greater than 0. If you specify a negative integer value for the length argument, the mapping runs without

SQL ELT optimization

.

When you push the EXP() function to the Google BigQuery database and specify a value of the Numeric or Double data type for the exponent argument, you might encounter a data mismatch in the decimal values when you compare a mapping that runs with full

SQL ELT optimization

and a mapping that runs without

SQL ELT optimization

.

When you push the RPAD() or LPAD() function to the Google BigQuery database, you must specify a value of the String data type in the first_string argument. If you specify a value other than the String data type in the first_string argument, the mapping runs without

SQL ELT optimization

.

When you push the RPAD() or LPAD() function to the Google BigQuery database and specify an empty string in the second_string or third_string argument, the mapping runs without full

SQL ELT optimization

.

When you push the REPLACECHR() function to the Google BigQuery database to write Numeric data to the Google BigQuery target, you can see a data mismatch between the results when you compare a mapping that runs with full

SQL ELT optimization

against a mapping disabled for

SQL ELT optimization

.

When you push the REPLACECHR() or REPLACESTR() function to the Google BigQuery database, the microseconds available in the timestamp data is considered in the casted string for a mapping that runs with full

SQL ELT optimization

.

When you push the REPLACESTR() or REPLACECHR() function to the Google BigQuery database and specify special characters in the format arguments in a nested function, ensure that the nested function does not contain a single backslash. You can use a double backslash in the nested function.

When you push the REPLACESTR() or REPLACECHR() function using an Expression transformation with the data/time data types to Google BigQuery using full

SQL ELT optimization

, the default date format of the data/time data types returned for a mapping with

SQL ELT optimization

is YYYY-MM-DD HH24:MI:SS.US, whereas for a mapping without

SQL ELT optimization

is MM/DD/YYYY HH24:MI:SS.US.

When you push down the Is_Number() function for Float data types with NaN, -inf, and +inf values, the Is_Number() function returns true.

When you push the Is_Date() function using an Expression transformation with the YYYY-MM-DD format and data contains data types with the YYYY-MM-DD and YYYY/MM/DD formats , the Is_Date() function returns true only for YYYYY-MM-DD. When you push the Is_Date() function with the YYYY/MM/DD format and data contains data types with the YYYY-MM-DD and YYYY/MM/DD formats , the Is_Date() function returns true only for YYYY/MM/DD.

When you push the Is_Number() function to process in Google BigQuery from a Secure Agent machine on Windows, the Is_Number() function returns false for the following format: '45.45d-2'

When you use the TO_CHAR(String) function, the string value must not contain a backslash (\). Else, the mapping fails.

When you configure an IN function that returns a value of string data type in an Expression transformation and writes the value to an integer data type in full

SQL ELT optimization

, the mapping fails with the following error:

When you configure an IN function that returns a value of integer data type in an Expression transformation and writes the value to an integer data type in full

SQL ELT optimization

, the mapping fails with the following error:

When you configure an IN function that returns a value of integer data type in an Expression transformation and writes the value to any data type in the target in source

SQL ELT optimization

, the mapping fails with the following error:

When you run a mapping enabled with full

SQL ELT optimization

and if the arguments are null in the DATE_COMPARE function, the mapping runs without

SQL ELT optimization

.

When you configure a mapping enabled with full

SQL ELT optimization

, the mapping switches to source

SQL ELT optimization

or runs without

SQL ELT optimization

if the operands of

+

,

-

,

*

, or

/

operators contain NULL in the expression, aggregator, or filter transformations.

When you push the REG_REPLACE() function to the Google BigQuery database and specify a backslash (

\

) in the column name or a nested function, the mapping fails with the following error:

When you push the REG_REPLACE() function to the Google BigQuery database, ensure that the column name or nested function does not contain a single backslash. The function only supports single backslashes that are followed by a digit or another backslash.

You cannot use the following escape sequences with the regular expression in the REG_REPLACE() function:

Escape sequence	Description
\1	Back reference
\b	Backspace (use \010 )
\cK	Control char ^K (For example, use \001 )
\e	Escape (use \033 )
\g1	Back reference
\g{1}	Back reference
\g{+1}	Back reference
\g{-1}	Back reference
\g{name}	Named back reference
\g<name>	Subroutine call
\g'name'	Subroutine call
\k<name>	Named back reference
\k'name'	Named back reference
\lX	Lowercase X
\ux	Uppercase x
\L...\E	Lowercase text ...
\K	Reset beginning of $0
\N{name}	Named Unicode character
\R	Line break
\U...\E	Upper case text ...
\X	Extended Unicode sequence
\%d123	Decimal character 123
\%xFF	Hex character FF
\%o123	Octal character 123
\%u1234	Unicode character 0x1234
\%U12345678	Unicode character 0x12345678

You cannot use the following empty strings with the regular expression in the REG_REPLACE() function:

Empty string	Description
\g	At beginning of subtext being searched
\G	At end of last match
\Z	At end of text, or before newline at end of text
(?=re)	Before text matching re
(?!re)	Before text not matching re
(?<=re)	After text matching re
(?<!re)	After text not matching re
re&	Before text matching re
re@=	Before text matching re
re@!	Before text not matching re
re@<=	After text matching re
re@<!	After text not matching re
\zs	Sets start of match (= \K )
\ze	Sets end of match
\%^	Beginning of file
\%$	End of file
\%V	On screen
\%#	Cursor position
\%'m	Mark m position
\%23l	In line 23
\%23c	In column 23
\%23v	In virtual column 23

When you configure the DATE_DIFF function in an Expression transformation and the transformation output is set to double or decimal data type in a mapping enabled with

SQL ELT optimization

, the function returns an integer value. However, when you run the mapping without

SQL ELT optimization

, the function returns a double value. Also, you might encounter a data mismatch when the transformation output is set to integer data type.

When you set the optional property

OptimizeCastsInPDO

in the Google BigQuery connection, you can compare the following data types in the DATE_DIFF function:

Date1 argument	Date2 argument	Format argument
Date	Date	Year, Month, and Day
Date	Datetime	Year, Month, Day, Hour, Minute, Second, Millisecond, and Microsecond
Datetime	Date	Year, Month, Day, Hour, Minute, Second, Millisecond, and Microsecond
Datetime	Datetime	Year, Month, Day, Hour, Minute, Second, Millisecond, and Microsecond
Time	Time	Hour, Minute, Second, Millisecond, and Microsecond
Timestamp	Timestamp	Day, Hour, Minute, Second, Millisecond, and Microsecond

When you set the format argument to year, month, nanosecond, or null in the DATE_DIFF function, the mapping runs with source

SQL ELT optimization

or without

SQL ELT optimization

.

When you configure DECODE or IFF functions along with AND, OR, or NOT IN operators in a mapping enabled with

SQL ELT optimization

, the mapping might fail.