NetSuite SQL Formula Cheat Sheet

A SQL CASE WHEN statement is the gateway to new types of Saved Searches that provide more dynamic outputs from heavily sliced datasets. Once you become proficient with this type of formula you open up a whole new way to segment data and can produce much more defined sets of results to more accurately meet the users’ needs.

The structure of the formula is one thing but remembering all the various syntax required for different specifications can be a struggle. Particularly if the only time you ever use SQL is in a NetSuite Saved Search. The following is a list of some commonly used comparison operators that will hopefully save you twenty minutes of Googling next time you need to create a CASE WHEN formula field. Bookmark this page so you can easily come back any time you need it.

CASE WHEN EQUALS

=

Starting out simple. You can use this when you want to specify the exact result of a field

CASE WHEN NOT EQUAL

!=

If you want to specify a value that the field is not then you do so like this. For example, if I am analyzing variances, I want to see all values that do not equal 0.

CASE WHEN GREATER THAN OR LESS THAN

> or <

CASE WHEN GREATER THAN OR EQUAL TO

>=

If I want to flag invoice amounts that are greater than or equal to 1,000,000 then I would do so using >=.

CASE WHEN NULL

IS NULL

Using this term will specify fields that are empty – ie. contain no value. Notice here that we are using IS instead of =.

CASE WHEN IS NOT NULL

IS NOT NULL

This is used when you want to identify fields that are containing a value.

CASE WHEN CHECKBOX

= ‘1’ or = ‘0’

The TRUE and FALSE of a checkbox is identified as 1 and 0 respectively.

CASE WHEN IS SPECIFIC MONTH

(to_char({datefieldname},'mm') = 1

This to_char converts the date into the month only. We are then specifying the number month that we are interested in. In the example above 1 is January. We could insert the date created field id to only show transactions, for example, that were created in January

CASE WHEN IN STRING

IN (1,2,3)

When you are looking for one of many values then you can use IN in replacement for =. You then list your values within a set of brackets and separated by commas. In the above example I am including any fields where the value is 1, 2 or 3.

CASE WHEN MULTIPLE CONDITIONS

You may want to give two necessary conditions for a specific result. In this scenario you can simply state both conditions separated by AND. For example –

CASE WHEN condition AND condition

If you need to show different results for a number of different conditions, you can simply add another WHEN. An example structure would be as follows –

CASE WHEN condition THEN result WHEN anothercondition THEN anotherresult

The above is by no means a complete list. There is so much more to be learnt if you are really going to get the most out of the CASE WHEN formula in a NetSuite Saved Search. You can use the above as a reference next time your mind draws a blank on a specific requirement you haven’t used in a while.

Don’t forget you can also search NetFreak for other articles about Saved Searches and SQL formula. Try the following article that gives some further examples of what can be done with a date field –

NetSuite CASE WHEN Formula With Multiple Criteria and Date Parameters

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *