Case Statements: Your Secret Weapon for Data Transformation

Case statements have saved my day more than once. If you ever need to evaluate one or more conditions as part of the criteria or results set, you may want to look at using case statements.

Here are a few examples:

---

Normalize shipping weight units

(
  CASE 
    WHEN {item.weightunit} = 'lb' THEN {item.weight} * 16 
    WHEN {item.weightunit} = 'oz' THEN {item.weight} 
    ELSE 0
  END
) * {quantity}

---

Decoding Error Codes: Translating to Business Language

CASE
    WHEN INSTR({custbody_integr_error}, 'Nested connector call returned more than 1 document.') > 0 THEN 'LOT/Serial Issue'
    WHEN INSTR({custbody_integr_error}, 'shipTo.address1') > 0 THEN 'Shipping Address Line 1 Issue'
    WHEN INSTR({custbody_integr_error}, 'Please configure the inventory detail') > 0 THEN 'LOT/Serial Issue'
    WHEN INSTR({custbody_integr_error}, 'Invalid issueinventorynumber reference key') > 0 THEN 'Assembly Build Issue'
    WHEN INSTR({custbody_integr_error}, 'Duplicate External ID') > 0 THEN 'Duplicate Order Detected'
    WHEN INSTR({custbody_integr_error}, 'Backorder') > 0 THEN 'Backorder'
    WHEN INSTR({custbody_integr_error}, 'Please enter value(s) for: Serial/Lot Number') > 0 THEN 'LOT/Serial Issue'
    WHEN INSTR({custbody_integr_error}, 'All lines of sublist itemList have to be specified when replace All is requested') > 0 THEN 'Kit Issue'
    WHEN INSTR({custbody_integr_error}, 'No valid skus found') > 0 THEN 'External System has SKU issue'
    WHEN INSTR({custbody_integr_error}, 'The operation exceeded the time limit and the record was not processed') > 0 THEN 'API Time Limit Exceeded'
    WHEN INSTR({custbody_integr_error}, 'The operation exceeded the time limit and the status of the record is unknown') > 0 THEN 'API Time Limit Exceeded'
    WHEN INSTR({custbody_integr_error}, 'NetSuite Sales Order Update Not Successful') > 0 THEN 'NetSuite Update Issue (Pending Approval?)'
    WHEN INSTR({custbody_integr_error}, 'NetSuite Sales Order Mark As Fulfilled Update Not Successful.') > 0 THEN 'SO Already Fulfilled / Update Not Successful'
    WHEN INSTR({custbody_integr_error}, 'Connection reset') > 0 THEN 'Connection reset'
    ELSE 'Unknown'
END

---

Unveiling the Magic: Transforming Time Differences into Business Insights with SQL CASE Statements

This statement may seem more complex than it is. It could be used in a Saved Search to allow business users identify Sales Orders that have breached a defined SLA. Here’s a breakdown:

It checks for a specific condition related to the maximum value in the {custrecord_status_code} field. If the condition is met, it performs a series of operations on the time difference between the current date and the maximum date in the {custrecord_status_entered_date} field and returns a numeric result. If the condition is not met, it returns NULL.

1. It checks if the maximum value in the field {custrecord_status_code} is one of the values specified in the list ('000', '110', '111', ..., '530').

2. If the condition in step 1 is met, it calculates a numeric value using the following steps:

   • It calculates the time difference in minutes between the current date and time (represented as {today}) and the maximum value in the field {custrecord_status_entered_date}. This difference is multiplied by 1440 to convert it to minutes (1 day = 1440 minutes).

   • It converts the result of the above calculation to a string (using TO_CHAR).

   • It extracts a substring of the resulting string starting from position 0 until the first space character is encountered (using SUBSTR and INSTR).

   • It replaces any plus sign ('+') with an empty string, effectively removing it (using REPLACE).

   • Finally, it converts the modified string to a number (using TO_NUMBER).

3. If the condition in step 1 is not met, it does not return any value (NULL).

CASE
    WHEN (MAX({custrecord_status_code}) IN ('000','110','111','220','230','240','250','450','460','470','480','510','520','530'))
    THEN TO_NUMBER(
        REPLACE(
            SUBSTR(
                TO_CHAR(
                    (({today} - MAX({custrecord_status_entered_date})) * 1440),
                    0,
                    INSTR(
                        (({today} - MAX({custrecord_status_entered_date})) * 1440),
                        ' '
                    )
                ),
                '+',
                ''
            )
        )
    )
END

---