SQL Server Execute - Dealing With No Results
Dear Snaplogic Community
I am calling an SQL Server Execute to return the field value of “CLI_SUR_NM” i.e. surname in the table based on a “$FinalIngenPolicyNo” primary key fed by a mapper. If the ...
Forum Discussion
The router looks for the field value “CLI_SUR_NM” but returns an error in the router when no records are returned. What can i do to handle this zero recordsreturned?
Thanks again for your help.
You’ll need to check if the property is in the document. You can check for that using the in operator, like so:
'CLI_SUR_NM' in $
Or, by using the get() method, like so:
$.get('CLI_SUR_NM') != null
You probably want to check the ‘First match’ option as well, so that every route is not evaluated.
I had a similar issue but struggled to get the ‘check for property’ expression to work correctly. I worked around it by adding another SQL Execute step with the same WHERE clause but a count(*) as total in the Select. The Total column will still be passed through regardless of whether there is data present as it will simply contain 0 if no records are returned. I then configured the router to say if total= 0 do one thing, if total > 0 do something else.
