Trying (unsuccessfully) to use the

WITH ... AS

clause in a search formula to alias a calculation for use multiple times in the formula (to make the formula more readable) Is there another way to do this? For reference my formula is something like:

WITH delta AS (NVL({systemnotes.newvalue},0)-NVL({systemnotes.oldvalue},0)) LISTAGG(CASE WHEN delta < 0 THEN {number}||': '||delta END, '<br>')

Is this a Saved Search or SuiteQL query?

saved search it's for a sales order reallocation audit search (thread in #general)

SuiteQL example with

WITH

and

LISTAGG

, don't know if it works with saved searches:

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, ', ') WITHIN GROUP (ORDER BY t2.uniquekey) AS transactionline_uniquekeys
FROM     t2
GROUP BY t2.transaction_id
ORDER BY t2.transaction_id

Thanks Shai 👍 maybe WITH clause only takes result sets/tables and not values... appreciate your help