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.
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
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.
b100percent
The following is from SuiteAnswers:
Resolve Error: “Syntax Error in the SQL Statement” When Using LISTAGG Function in ODBC
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.