SQL Server Execute - Dealing With No Results

[andre_mangatal]

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 ID primary key is not found then how do I trap this scenario in the snaplogic snaps? What is a good way to deal with no results from an sql execute snap? If it returns no results and i map the “$CLI_SUR_NM” value I get an error.

Thank you very much!

[andre_mangatal]

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.

[tstack]

andre.mangatal:

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?

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.

[sichetts]

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.