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:
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.
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.