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
2 Answers

In Netsuite 2022.1, when I run the following query using the N/query.runSuiteQL() function, I am able to get results successfully.  I’m not sure if this also works with SuiteAnalytics Connect.

SELECT

LISTAGG (tranid, ',') WITHIN GROUP(ORDER BY tranid) AS document_numbers,

trandate AS date

FROM

transaction

WHERE

rownum < 10

GROUP BY trandate

It would appear that there is a character limit on each result, although I’m not sure what it is. I would recommend starting with a small number of results to make sure your formula works and then increasing the results from there.

Rookie Answered on July 12, 2022.
Add Comment

Your Answer

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