Skip Navigation
Expand
Using "concat" function in the report returns empty/blank records
Answer ID 8338   |   Last Review Date 11/18/2018

Why do I receive empty or blank data in my report but should receive concatenated string data?

Environment:

Analytics
Oracle Service Cloud, All supported versions

Resolution:

CONCAT is a standard SQL function that concatenates two strings to return a concatenated string. When creating report it is very important to remember that concatenating a NULL value to a string results in a NULL value.

For example we need a contact information to be returned in one string:

concat(contacts.first_name,'\n',contacts.last_name,'\n',contacts.city,'\n',contacts.street,'\n',contacts.state,'\n',contacts.postal_code)

If even one of those fields is empty then a blank record will be displayed because the concatenated string will be nulled.

This is the industry-wide SQL standard.


For additional information, refer to the 'Functions' section in online documentation for the version your site is currently running.  To access Oracle Service Cloud manuals and documentation online, refer to the Documentation for Oracle Service Cloud Products.

Available Languages for this Answer:

Notify Me
The page will refresh upon submission. Any pending input will be lost.