I'm trying to get the product of 2 fields in the 'ELSE' portion of a CASE statement of a Formula(Text) row in a Netsuite saved search, but I keep getting 'ERROR: Invalid Expression' when I run the search. Current formula is:
CASE WHEN {binonhandcount} = 0 THEN '0' ELSE NVL({binonhandcount}, 0) * NVL({locationaveragecost}, 0) END
I've tried to simplify it by doing something like this:
CASE WHEN {binonhandcount} = 0 THEN '0' ELSE 1 + 1 END
But it still fails with an invalid expression error. All of the Googling I've done leads me to believe that this should work, but I can't seem to find my mistake. I'm hoping the extra eyes here can give me a kick in the right direction. Thank you.
The data type returned from the formula needs to match the Formula type selected. You can correct your formula by setting it to a Formula (Numeric) type and simply removing the quotes around the '0' after the first THEN:
CASE WHEN {binonhandcount} = 0 THEN 0 ELSE NVL({binonhandcount}, 0) * NVL({locationaveragecost}, 0) END
Or if you really want a text formula for some reason, you can wrap the ELSE statement in a TO_CHAR function:
CASE WHEN {binonhandcount} = 0 THEN '0' ELSE TO_CHAR(NVL({binonhandcount}, 0) * NVL({locationaveragecost}, 0)) END