This site was built to compliment the existing NetSuite Professionals Slack Community by adding a means for members to easily find answers to common questions and to create a searchable database of those questions and answers.

NetSuite Professionals

I know the LISTAGG function is in the <https://docs.oracle.com/en/cloud/saas/netsuite/ns-online-help/section_158513731864.html#subsect_156466574268|Unsuppoted Functions> list.  But sometimes it works, and other times it gives a syntax error.  Does anyone know of a work-around or alternative?

I was like….CONVINCED it didn’t work for ages and wrote a complex chunk of SQL to make a bitmask and turn that into my equivalent.  Was a pain in the butt.  The last few times I’ve tried LISTAGG it has worked tho.

Make sure you use ”on overflow truncate” as it can fail if any value becomes longer than 3-4000 characters.

<@UP0GS40AF> -- how do you specify ”on overflow truncate”?

```LISTAGG(TL.uniqueKey, ',' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT) WITHIN GROUP(ORDER BY TL.uniqueKey)```

Full example:
```WITH t2 AS (
  SELECT     transaction.id AS transaction_id, transactionline.uniquekey
  FROM       transaction
  INNER JOIN transactionline ON (transactionline.transaction = transaction.id)
  FETCH FIRST 1000 ROWS ONLY
)
SELECT   t2.transaction_id,
         LISTAGG(t2.uniquekey, ', ' ON OVERFLOW TRUNCATE '...' WITHOUT COUNT)
         WITHIN GROUP (ORDER BY t2.uniquekey) AS transactionline_uniquekeys
FROM     t2
GROUP BY t2.transaction_id
ORDER BY t2.transaction_id```