In New to NetSuite | SuiteQL Overview and New to NetSuite | Understanding SuiteQL Syntax articles, we explored the fundamental concepts, capabilities, and structure of SuiteQL.
SuiteQL Syntax for Advanced Queries goes beyond simple selections and joins to deliver robust, flexible, and analytical queries within database. SuiteQL’s capabilities are similar to standard SQL (with some NetSuite-specific constraints). One of these advanced features is NVL2.
The NVL2 is a function that checks whether a value is NULL or not NULL, then returns one value if the field contains data and a different value if the field is empty (NULL).
Syntax
NVL2(expression, value_if_not_null, value_if_null)
Sample
NVL2(transaction.memo, 'Has Memo', 'No Memo')
means:
- If transaction.memo is not NULL - returns 'Has Memo'
- If transaction.memo is NULL - returns 'No Memo'
For example, in SuiteQL:
SELECT
id, -- Returns the internal ID of the customer
NVL2(
email, -- Checks whether the email field is NULL or not
email, -- If email is NOT NULL, returns the email address
'No email' -- If email is NULL, returns the text 'No email'
) AS email_display -- Names the resulting column "email_display"
FROM customer -- Retrieves the records from the customer table
DISCLAIMER: The sample code described herein is provided on an "as is" basis, without warranty of any kind, to the fullest extent permitted by law. Oracle + NetSuite Inc. does not warrant or guarantee the individual success developers may have in implementing the sample code on their development platforms or in using their own Web server configurations.
Oracle + NetSuite Inc. does not warrant, guarantee or make any representations regarding the use, results of use, accuracy, timeliness or completeness of any data or information relating to the sample code. Oracle + NetSuite Inc. disclaims all warranties, express or implied, and in particular, disclaims all warranties of merchantability, fitness for a particular purpose, and warranties related to the code, or any service or software related thereto.
Oracle + NetSuite Inc. shall not be liable for any direct, indirect or consequential damages or costs of any type arising out of any action taken by you or others related to the sample code.
Stay tuned for upcoming articles on how to use SuiteQL Syntax in SuiteCloud Product Area. Stay updated by following the New to NetSuite > SuiteCloud category to receive notifications whenever new articles are published.
