oracle-databasenetsuite

Oracle Netsuite Question: how to combine multiple optional fields with delimiter


In short, I'm trying to replicate excel's TEXTJOIN function in Oracle Netsuite.

More detailed: I have a field in Oracle Netsuite that needs to do the following:

Part 1: If certain criteria are met, write in some text for Part 1, else remain blank Part 2: If certain criteria are met, write in some text for Part 2, else remain blank Part 3: .... All the way to Part n

There's no guarantee that any of these parts are there. Once all parts are there (or not), I need to join them with a delimiter (the bar "|"). So it could be just "Part 1", or maybe "Part 1|Part 2", or "Part 2|Part 4", or "Part 1|Part 2|Part 3|....|Part n"

How do I do this using Oracle Netsuite's Functions? Currently I only need to handle up to 2, but I likely will need to go back and write this up to 11, or even more.

Any ideas?

Currently I have the following parts:

Part 1:

CASE WHEN {shiptype} IS NULL THEN '' ELSE 'SHIP='||{shiptype} END

Part 2:

CASE WHEN {packagetype} IS NULL THEN '' ELSE 'PACKAGEID='||{packagetype} END

so the plan for just these two is to do this:

CASE WHEN {shiptype} IS NULL THEN '' ELSE 'SHIP='||{shiptype} END
CASE WHEN {shiptype} IS NULL OR {packagetype} IS NULL THEN '' ELSE '|' END
CASE WHEN {packagetype} IS NULL THEN '' ELSE 'PACKAGEID='||{packagetype} END

which would work for just these two, but i'm not sure how this would work for a third, or fourth, etc. Thoughts?


Solution

  • This is really simple. First of all you need to pick a type and validate with case condition and then join case results together.

    LTRIM(
        CASE WHEN {shiptype} IS NOT NULL THEN '|SHIP='||{shiptype} ELSE '' END ||
        CASE WHEN {packagetype} IS NOT NULL THEN '|PACKAGEID='||{packagetype} ELSE '' END ||
        CASE WHEN {othertype} IS NOT NULL THEN '|OTHER='||{othertype} ELSE '' END
    , '|')
    

    Lastly, LTRIM(Data, '|') removes the leading | . Cheers.