When working with SuiteQL queries, you may notice that some fields return null values if no data has been entered. For example, if a Customer doesn’t have a phone number on record, the query output for that field will simply be blank. While technically correct, blank values can make reports harder to read and may cause issues when exporting or integrating data with other systems.
To make your query results more meaningful and user-friendly, you can use the COALESCE() function. This function allows you to replace null values with a default value of your choice—such as "Not Provided," "N/A," or even a placeholder number.
Example: Show Phone Number or Default Text
The following query returns the customer ID and displays the phone number if available. If the phone field is empty, it will instead display “Not Provided.”
SELECT
entityid,
COALESCE(phone, 'Not Provided') AS phone_number
FROM
customer
Use COALESCE anywhere you want to ensure missing values are replaced with something clear and consistent. This small tweak can save time and reduce confusion when reviewing query results.
To easily execute a SuiteQL query, see NetSuite Admin Tip | Running SUITEQL Using a Suitelet
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.
We’d love to hear how you integrate SuiteQL into your NetSuite processes! Share your insights and experiences in the NetSuite Admin Corner.