Hi,

I have a scenario where "External Reference" is a custom field under the Incident Template and also under a Request Template. I am trying to merge these two fields in a report based on the principle of checking if the field under Incident Template is a NULL, then checking the Request Template for a NULL and assigning a default value if both are NULL.

Part (1) - NULLS

The official instructions on the Query Editor page says that NULLS will be converted:

1. Default value for null data : COALESCE(COLUMN_NAME,'Unassigned')

However, this doesn't seem to be the case. The output visible from SELECT * FROM WorkOrder_Fields produces 'Not Assigned' rather than 'Unassigned'

Running "SELECT TOP(5) * FROM WorkOrder_Fields WHERE udf_char21 IS NOT NULL"

still gives values of 'Not Assigned', exactly as if NULLs were not being excluded. Note that the first workorderid is different, indicating that some processing has occurred. Checking the field under the Requester's view, the field is blank (in other words, it is not displaying "Not Assigned" because someone has typed in that text).

Part (2) - Specific query

This is the specific query I am trying to run:

Sample output:

ExtRef
	Not Assigned
	No Project
	No Project
	No Project
	No Project
	4031
	Not Assigned