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!
Do you have the “Pass Through” option checked in the Execute snaps properties? Can you share the full configuration for the Execute snap.
Also, I wanted to mention that I don’t think you need to make the SQL Statement an expression. I think you should be able to write the last bit as follows:
CFPOL.POL_ID = $FinalIngenPolicyNo;
The snap should substitute references like that automatically.
Hi Tim
Thanks for the reply. Yes I do have “Pass Trhough” checked as seen below in the screenshot. If it returns nothing what’s the best way to handle this? Thanks
Noted on not make the SQL Statement an expression
Thank you!
In your first post, the screenshot shows the Execute snap not generating any preview documents. But, you don’t have ‘Execute during preview’ checked for the snap, so it won’t running during validation/preview. Can you select that and try again?
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 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.