OK, it’s been a lon day already but this should be a no brainer. I have a customer who wants to send a report to her dealers BUT if the location quantity available is more than 100 to just display 100+. Easy right?

I created a formula field (Text) and used the following:

CASE WHEN {locationquantityavailable} >100 THEN ‘100+’ ELSE {locationquantityavailable} END

I get ERROR: Invalid Expression. To make sure I get the syntax correct I changed it to (just to test):

CASE WHEN {locationquantityavailable} >100 THEN ‘100+’ ELSE ‘Less than 100’ END

Now I do not get the Invalid Expression and the results are correct. So I really Need to get the {locationquantityavailable} to work.

One other very strange thing I get as I was trying to get the syntax correct, I accidentally placed the {locationquantityavailable} after the else within the single quotes and believe it or not, this is what was displayed to the screen for all records where the available is less than 100:

(nullif(round(itemlocationmap.ronhandavail/nvl(stockunit.mconversionrate,1),5),0))

Where the heck did THAT come from?
This is a cached copy. Click here to see the original post.