Nested Case When

user_13938124 0
Viewed 36

I cannot get the '16 Day Delivery' to show up on orders over 9999.99lbs in a Netsuite saved search. The first part work fine for over 150.99. What am I missing?

CASE WHEN{custcol_item_weight}*{quantity}>'150.99' THEN '8 Day Delivery' WHEN {custcol_item_weight}*{quantity}>'9999.99' THEN '16 Day Delivery' ELSE '4 Day Delivery' END
netsuite case-when
2 Answers

When case conditions overlap (a number greater than 9999.99 is also greater than 150.99), the first condition met determines the return value. For your use case, catch the condition with the highest value first:

CASE 
WHEN {custcol_item_weight}*{quantity} > '9999.99' THEN '16 Day Delivery' 
WHEN {custcol_item_weight}*{quantity} > '150.99' THEN '8 Day Delivery' 
ELSE '4 Day Delivery' 
END
user_137099080

Any value that's greater than 9999.99 will also be greater than 150.99, so the second when clause will never be hit. For the first term, you should check if the value is also not larger than 9999.99:

CASE WHEN {custcol_item_weight}*{quantity} > '150.99' AND {custcol_item_weight}*{quantity} < '9999.99'THEN '8 Day Delivery' 
     WHEN {custcol_item_weight}*{quantity} > '9999.99' THEN '16 Day Delivery' 
     ELSE '4 Day Delivery' 
END
user_24227760
Related