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.

How do I end a project when sql stmt returns 0 rows

Postby andbiond » Tue May 17, 2011 3:59 pm

I would like to end a project if the initial sql selects 0 rows. (This project selects rows from a database, formats the output & writes out the data to a text file.

Thanks
Dino
andbiond
 
Posts: 1
Joined: Tue May 17, 2011 2:29 pm

Re: How do I end a project when sql stmt returns 0 rows

Postby Support_Duane » Wed May 18, 2011 11:04 am

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:

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_Duane
Support Specialist
 
Posts: 65
Joined: Thu Mar 05, 2009 3:49 pm

Re: How do I end a project when sql stmt returns 0 rows

Postby Support_Duane » Wed May 18, 2011 2:21 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.
Support_Duane
Support Specialist
 
Posts: 65
Joined: Thu Mar 05, 2009 3:49 pm


Return to Community Forum

Who is online

Users browsing this forum: No registered users and 1 guest

cron