Solved
ExecuteSQLRecord Processor returns a NULL value but same query in SQL Server returns a value
I am having an issue using the ExecuteSQLRecord processor and writing to JSON. For some reason the results returns a NULL value for one of my fields in the FlowFile results. However, if I execute the query directly in SQL Server, I get all my results with no NULL fields. I checked and there are no special characters, single quotes are escaped and nothing looks to be out of the ordinary.
SQL Select Statement:
Select
emailTo = REPLACE(e.Email_Recipients, ';', ','),
emailFrom = e.Email_Sender,
emailSubject = e.Email_Subject,
emailMessage = CASE
WHEN c.ValidationCount = 0 THEN
REPLACE(e.Email_Message, '{0}','${filename}')
WHEN c.ValidationCount = 1 THEN
CONCAT(REPLACE(e.Email_Message, '{0}','${filename}'), '<p>There is 1 validation error in this file</p>', v.Validations)
ELSE
CONCAT(REPLACE(e.Email_Message, '{0}','${filename}'), '<p>There are ', c.ValidationCount, ' validation errors in this file</p>', v.Validations)
END,
v.Validations
FROM [${DataCollection}].[dbo].[T_SystemEmailNotifications] e
Join (
SELECT ve.File_ID,
STUFF(
(
SELECT CONCAT(' <p>', REPLACE(v1.Comments, '''', ''), '</p>') AS [text()]
FROM [dbo].[T_FileValidationLog] v1
WHERE ve.File_ID = v1.File_ID
ORDER BY ve.File_ID
FOR XML PATH (''), TYPE
).value('text()[1]','nvarchar(max)'), 1, 1, '') [Validations]
FROM [${DataCollection}].[dbo].[T_FileValidationLog] ve
WHERE [File_ID] = ${FileID}
GROUP BY ve.File_ID
) v
ON ${FileID} = v.File_ID
Join (
SELECT File_ID, Count(*) As ValidationCount
FROM [${DataCollection}].[dbo].[T_FileValidationLog]
WHERE [File_ID] = ${FileID}
GROUP BY File_ID
) c
ON v.File_ID = c.File_ID
Where e.Email_Type = '${EmailType}'
The results returned to JSON are:
[ {
"emailTo" : "recipient1@somewhere.com,recipient2@somewhere.com",
"emailFrom" : "DoNotReply@somewhere.com",
"emailSubject" : "Correct Email Subject",
"emailMessage" : "<p>The following file was unable to be processed and was rejected:</p><p>filename.xml</p><p>There are 40 validation errors in this file</p>",
"Validations" : null
} ]
"emailTo" : "recipient1@somewhere.com,recipient2@somewhere.com",
"emailFrom" : "DoNotReply@somewhere.com",
"emailSubject" : "Correct Email Subject",
"emailMessage" : "<p>The following file was unable to be processed and was rejected:</p><p>filename.xml</p><p>There are 40 validation errors in this file</p>",
"Validations" : null
} ]
However, if I run the query using the FlowFile attribute values directly in SQL Server, I get the full results, including the Validations field.
Results for Validations field from SQL Server:
<p>Validation Error: The NOTREPORTED attribute is not declared. - Line: 193</p>
<p>Validation Error: The required attribute NotReported is missing. - Line: 193</p>
<p>Validation Error: The NOTREPORTED attribute is not declared. - Line: 195</p>
<p>Validation Error: The required attribute NotReported is missing. - Line: 195</p>
<p>Validation Error: The NOTREPORTED attribute is not declared. - Line: 197</p>
<p>Validation Error: The required attribute NotReported is missing. - Line: 197</p>
<p>Validation Error: The required attribute NotReported is missing. - Line: 193</p>
<p>Validation Error: The NOTREPORTED attribute is not declared. - Line: 195</p>
<p>Validation Error: The required attribute NotReported is missing. - Line: 195</p>
<p>Validation Error: The NOTREPORTED attribute is not declared. - Line: 197</p>
<p>Validation Error: The required attribute NotReported is missing. - Line: 197</p>
I only included the first few rows of Validations as no need for long content.
The results will be used to flow into an EvaluateJSONPath to add the values as attributes. The to a PutEmail processor with a mime type of text/html. The PutEmail is working fine with the results , but the Validations are not there are they are returned as NULL from the ExecuteSQLRecord processor.
The only characters I can see that could cause issue are the : . - which I believe should be OK.
Thank you for any insight.
