Changes to SuiteAnalytics Connect Return Values and String Types

On March 19, 2019, the software version of the SuiteAnalytics Connect server was upgraded for testing purposes. The upgrade applied to sandbox and release preview accounts only, and no user intervention is required. Production accounts were upgraded in June 2019. The upgrade changed the data types, precision, and display sizes for the return values of some functions and string types used in SuiteAnalytics Connect.

Changes resulting from the upgrade are as follows:

Previous behavior	Current behavior	Description
Numeric functions return INTEGER values. Display size: 11 Precision: 10	Numeric functions return BIGINT values. Display size: 20 Precision: 19	Affected functions: Numeric functions such as count(), abs(), pow(), ceil(), floor(), and so on.
The return values display sizes and precision equal to the original column size.	The return values display sizes and precision equal to the resulting string size.	Affected functions: All string functions such as substring() and left(). For example, substring(col, 1, 30) returns a value with a precision and display size of 30. Exception: the right() function has a fixed precision and display size of 4000.
The return values display maximum sizes and precision for data types such as WVARCHAR and VARCHAR2.	The return values display sizes and precision equal to the actual size of the column.	Affected functions: nvl()
The return values did not display sizes and precision based on the number of repeats.	The return values display sizes and precision based on the number of repeats.	Affected functions: repeat() The return values display sizes and precision based on the number of repeats. For example, repeat(full_name, 2) returns a value with a display size of 8000, and repeat(full_name, 1) returns a value with a display size of 4000.
When col is null, the data type is CHAR and the display size and precision is 0.	When col is null, the data type is VARCHAR and the display size and precision is 1.	Affected functions: nvl(). When col is null, this change increases the display size and precision of the return values from 0 to 1.
When using negative values to represent the number of characters to extract, the functions return an empty string.	When using negative values to represent the number of characters to extract, the functions return an error message.	Affected functions: left() and substring(). For example, substring(full_name, 0, -1) returns an error. However, you can still use negative values to specify an index in the substring() function. For example, substring(col, -2, 1) does not return an error.
Using NULL returns values with a display size and precision of 0.	Using NULL after the upgrade returns values with a display size and precision of 1.	For example, select NULL as “col1” FROM table_name returns a value with a display size and precision of 1 for col1.
JOIN clauses should follow the ANSI SQL-92 standard.	JOIN clauses must be compliant with ANSI SQL-92. Queries that are not compliant may fail.	When running queries with JOIN clauses, you must follow the SQL-92 standard for the Connect Service.

Previous behavior

Current behavior

Description

Numeric functions return INTEGER values.

Display size: 11

Precision: 10

Numeric functions return BIGINT values.

Display size: 20

Precision: 19

Affected functions:

Numeric functions such as count(), abs(), pow(), ceil(), floor(), and so on.

The return values display sizes and precision equal to the original column size.

The return values display sizes and precision equal to the resulting string size.

Affected functions:

All string functions such as substring() and left().

For example, substring(col, 1, 30) returns a value with a precision and display size of 30.

Exception: the right() function has a fixed precision and display size of 4000.

The return values display maximum sizes and precision for data types such as WVARCHAR and VARCHAR2.

The return values display sizes and precision equal to the actual size of the column.

Affected functions: nvl()

The return values did not display sizes and precision based on the number of repeats.

The return values display sizes and precision based on the number of repeats.

Affected functions: repeat()

The return values display sizes and precision based on the number of repeats.

For example, repeat(full_name, 2) returns a value with a display size of 8000, and repeat(full_name, 1) returns a value with a display size of 4000.

When col is null, the data type is CHAR and the display size and precision is 0.

When col is null, the data type is VARCHAR and the display size and precision is 1.

Affected functions: nvl().

When col is null, this change increases the display size and precision of the return values from 0 to 1.

When using negative values to represent the number of characters to extract, the functions return an empty string.

When using negative values to represent the number of characters to extract, the functions return an error message.

Affected functions: left() and substring().

For example, substring(full_name, 0, -1) returns an error. However, you can still use negative values to specify an index in the substring() function. For example, substring(col, -2, 1) does not return an error.

Using NULL returns values with a display size and precision of 0.

Using NULL after the upgrade returns values with a display size and precision of 1.

For example, select NULL as “col1” FROM table_name returns a value with a display size and precision of 1 for col1.

JOIN clauses should follow the ANSI SQL-92 standard.

JOIN clauses must be compliant with ANSI SQL-92. Queries that are not compliant may fail.

When running queries with JOIN clauses, you must follow the SQL-92 standard for the Connect Service.

Related Topics