NetSuite: Saved Search That Returns Nth Result

I've just actually recently got into using the function regexp_substr. It didn't seem very relevant because most SQL functions already capture what customers are looking for. Until now. Someone was looking for a formula that would return either the smallest value, or next smallest, depending on some criteria. While I wasn't able to capture the entire request, I thought of a similar problem: How can I add the amounts of the two lowest SOs per customer?

The first thing that came to mind was that I needed to aggregate and rank the SO amounts using listagg. So that's what I did. 

• listagg({amount}, ' ') within group (order by {amount} asc)

Then, I thought of using my recent learnings on regexp_substr. It turns out, that actually did it. These were the formulas:

• REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)', 1, 1)
• REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)', 1, 2)

I used the space character as a delimiter for the listagg function. That allowed me to use the \s parameter to remove spaces. Setting the regexp_substr parameters also helped me pick the nth result of each array (yes, those would be represented by the numbers at the end).

So I just needed to add the two formulas together like so:

• REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)')+REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)',1,2)

There was a problem. The formula doesn't return values if customers only had 1 transaction. This took some time to get working. Why? I can't just NVL the entire thing and put amount as its second parameter right? It turns out, I can't -- because {amount} also needs to be aggregated. So I aggregated it:

• nvl(REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)', 1, 1)+REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)', 1, 2),listagg({amount}, ' ') within group (order by {amount} asc))

That however, turned out to be an error. Great. Now what? I eventually realized that listagg returns a string. So by using to_number, I actually got the thing to work. Perhaps NVL requires a number for its second parameter. This is the final form:

• nvl(REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)', 1, 1)+REGEXP_SUBSTR (listagg({amount}, ' ') within group (order by {amount} asc), '(\S*)(\s)', 1, 2),to_number(listagg({amount}, ' ') within group (order by {amount} asc)))

So what did we learn?

• You can use listagg to sort results, and delimit them by a space character.
• You can use regexp_substr to then pick the nth result with the use of '(\S*)(\s)' as a parameter.
• If you're already using a listagg function, you cannot use a non-aggregated field in the same formula; you'd have to listagg that field as well to make it work.
• NVL requires a number field; and to_number works on arrays with single number-like results.