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
Hi Tim
I selected execute during preview now. And only the original passes through. I want to write logic to do processing when no rows are returned from sql server execute e.g. using the router below.
Below is the original string which is returned when the primary key is not found.
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
inoperator, like so:'CLI_SUR_NM' in $Or, by using the
get()method, like so:$.get('CLI_SUR_NM') != nullYou 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.
