CODEWITHSUNDEEP
sqlcasenetsuite
Rick
Rick

Reputation: 21

Netsuite custom formula field using a CASE statement with multiple WHEN conditions returning "invalid expression"

I'm quite new to writing NetSuite sql case statements. I have been able to successfully write CASE statements with single WHEN conditions but when I include multiple WHEN conditions NetSuite returns "invalid expression" error. I am wondering if I need to separate the WHEN conditions with something other than a space. I've seen examples of nested statements with multiple WHEN conditions but NetSuite will not allow nesting of statements in formula fields. Here is my statement which is returning an error:

CASE WHEN {item.custitem_custid} IN (05,12) 
THEN {amount}*{item.custitem_sharedat50} 
WHEN {item.custitem_custid} IN (37,42,76) 
THEN {amount}*0.02 ELSE {amount}*{item.custitem_sharedat33} 
END

Here is my CASE statement with a single WHEN condition which works:

CASE WHEN {item.custitem_custid} IN (05,12) 
THEN {amount}*{item.custitem_sharedat50} 
ELSE {amount}*{item.custitem_sharedat33} END

Anybody with NetSuite experience have any ideas?

Upvotes: 1

Views: 36020

Answers (3)

Rayster
Rayster

Reputation: 41

You have to initiate and terminate each Case statement 

CASE WHEN {item.custitem_custid} IN (05,12) 
  THEN {amount}*{item.custitem_sharedat50} 
ELSE 
   CASE WHEN {item.custitem_custid} IN (37,42,76) 
      THEN {amount}*0.02 ELSE {amount}*{item.custitem_sharedat33} 
   END
END

This is the Case Statement I am using in a saved search that works

CASE WHEN {classnohierarchy} = 'Snow' THEN 
   CASE WHEN {custbody_hdr_cs_status} IN ('Follow-up','Follow-up & Term') THEN 0 
   ELSE 
      CASE WHEN {shipstate} IN ('NV','NY','PA','SD','UT','VA','WY') THEN 1 
      ELSE 0 
      END 
   END 
ELSE 0 
END

Upvotes: 4

Nitish
Nitish

Reputation: 796

Here is a sample code with multiple when . It works for me.

var formula = "case  when {internalid} IN (10555) then 'a'"
                 + " when {internalid} IN (10556) then 'b'"
                 + " when {internalid} IN (10557) then 'c'"
                 + " else 'd' end";
var filter = new nlobjSearchFilter('formulatext', null, 'contains', 'a').setFormula(formula);
var searchResult = nlapiSearchRecord('salesorder', null, filter);

Can you tell us the exact error that you got

Upvotes: 0

Benj Sicam
Benj Sicam

Reputation: 210

I've also bumped into the same issue. It seems like a bug in the system. You should file a support ticket to NetSuite. Until this is fixed, use DECODE instead.

Upvotes: 0

Related Questions