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

[andbiond]

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

[Support_Duane]

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]

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.