How do I end a project when sql stmt returns 0 rows
Post any question you may have in regards to GoAnywhere Director and let our talented support staff and other users assist you.
3 posts
Page 1 of 1
- Support Specialist
- Posts: 66
- Joined: Thu Mar 05, 2009 3:49 pm
Add another query after the select statement query to count the rows selected. Something like : select count(*) from library.filename. If your select statement has a where clause, add that to this statement also.
Add a Set Variable version 1.0 task (you’ll have to click the Include Prior Versions box in the light blue menu bar in order to be able to see this task) Name the variable whatever you choose, and fill it with the first column of the rowset specified in the above query.
Add a Raise Error task and condition it on your variable being equal to zero or, if you don’t want your project to end in error just because there were no records found, condition all of the following tasks to execute only if your variable is not equal to zero.
Here’s an XML code snippet that shows an example of both:
Add a Set Variable version 1.0 task (you’ll have to click the Include Prior Versions box in the light blue menu bar in order to be able to see this task) Name the variable whatever you choose, and fill it with the first column of the rowset specified in the above query.
Add a Raise Error task and condition it on your variable being equal to zero or, if you don’t want your project to end in error just because there were no records found, condition all of the following tasks to execute only if your variable is not equal to zero.
Here’s an XML code snippet that shows an example of both:
Code: Select all
<sql resourceId="resourceID" version="1.0">
<query outputVariable="data">
<statement>select count(*) from ${libName}.${fileName}</statement>
</query>
</sql>
<setVariable name="recCount" value="${data[1]}" version="1.0" />
<raiseError version="1.0" executeOnlyIf="${recCount} eq 0">
<message>No records were found in the ${fileName} file in the ${libName} library</message>
</raiseError>
<print version="1.0" executeOnlyIf="${recCount} ne 0">
<![CDATA[
this project found ${recCount} records in the ${fileName} file in the ${libName} library
]]>
</print>
- Support Specialist
- Posts: 66
- Joined: Thu Mar 05, 2009 3:49 pm
You could also place an entry in the "When No Data Found" field. The options are "continue" (the default) and "error" which throws an error and, depending on how "on Error" is set, can end the project at that point.
3 posts
Page 1 of 1
