RE: Using LISTAGG or NS_CONCAT in a SuiteQL query

I am attempting to concatenate all the rows in a column into a single comma separated string, but I receive an “Invalid or unsupported search” error anytime I try to use the LISTAGG or NS_CONCAT functions in my query string. It works properly in a Saved Search using the following formula:

LISTAGG({number}, ‘,’)

But the following SuiteQL query throws an error:

SELECT LISTAGG(number, ‘,’) FROM transaction WHERE recordtype=’salesorder’;

Any help concatenating rows in a SuiteQL query would be greatly appreciated.

kylejones Rookie Asked on February 15, 2021 in SuiteAnalytics.

The following is from SuiteAnswers:

 

Resolve Error: “Syntax Error in the SQL Statement” When Using LISTAGG Function in ODBC

Published 06/12/2020 05:06 PM   |    Updated 03/31/2021 03:26 AM   |    Answer Id: 93558

Applies To 

Product:  NetSuite 2020.2

Scenario 

The user wants to use LISTAGG function on their ODBC query, however, they get the Error: “Syntax Error in the SQL statement”. 

Solution

The LISTAGG function is not supported by SuiteAnalytics Connect. It’s not compliant with SQL-92.

on May 31, 2021.
Add Comment
1 Answers

Not sure if this helps but in a saved search ns_concat only works with a Minimum or Maximum Summary Type.  The field subjected to the ns_concat function cannot be grouped by itself

Rookie Answered on February 15, 2021.
Add Comment

Your Answer

By posting your answer, you agree to the privacy policy and terms of service.