Evaluate rows numbers in a table

Description

The Evaluate rows numbers in a table action evaluates the number of rows in a table and lets you define the success condition that can be used to branch the workflow into success/error streams.

You can either just test the number of rows in a target table, or write a custom SQL statement.

For the success condition you can check the number of rows if they are:

Equal to
Different from
Smaller than
Smaller or equal to
Greater than
Greater or equal to

Options

Property Description

Property	Description
Action name	The name to use for this workflow action
Connection	The Relational database connection to use
Target schema	The database schema name
Target table name	The database table name
Success condition
Success when rows count	Equal to Different from Smaller than Smaller or equal to Greater than (default) Greater or equal to
Limit	the number of rows to evaluate in combination with the selected option above.
Custom SQL
Custom SQL	Check this option when you want to specify a custom SQL statement in the `SQL Script` field below.
Use variable substitution	Variables in the SQL statements will be substituted.
Clear list of result rows before execution	Clears the internal list of result rows before executing.
Add rows to result	Adds all rows returned from the SQL statement within the internal list of result rows. This list of result rows can then be used in subsequent workflow actions to loop over or e.g. within a pipeline by the Get rows from result transform.
SQL Script	The custom SQL script to use. The `Custom SQL` option above needs to be checked before this field is enabled.

Action name

The name to use for this workflow action

Connection

The Relational database connection to use

Target schema

The database schema name

Target table name

The database table name

Success condition

Success when rows count

Equal to
Different from
Smaller than
Smaller or equal to
Greater than (default)
Greater or equal to

Limit

the number of rows to evaluate in combination with the selected option above.

Custom SQL

Check this option when you want to specify a custom SQL statement in the SQL Script field below.

Use variable substitution

Variables in the SQL statements will be substituted.

Clear list of result rows before execution

Clears the internal list of result rows before executing.

Add rows to result

Adds all rows returned from the SQL statement within the internal list of result rows. This list of result rows can then be used in subsequent workflow actions to loop over or e.g. within a pipeline by the Get rows from result transform.

SQL Script

The custom SQL script to use. The Custom SQL option above needs to be checked before this field is enabled.

Result

What this action passes on to the next action. See Action results for how results travel through a workflow.

Result	Value
True	The number of rows meets the Success when rows count condition, with 0 errors.
False	The condition is not met: the number of errors is 0. When the table doesn’t exist, Target table name is empty (without Custom SQL), SQL Script is empty (with Custom SQL), the query fails, or the connection is empty, unknown or can’t be opened, the number of errors is 1.
Result files	Passed on unchanged.
Result rows	Without Custom SQL: passed on unchanged. With Custom SQL: Clear list of result rows before execution removes the previous result rows, also when Add rows to result is unchecked. Add rows to result adds the rows the query returns, also when the condition is not met.
Lines read	The number of rows counted.

Result

Value

True

The number of rows meets the Success when rows count condition, with 0 errors.

False

The condition is not met: the number of errors is 0. When the table doesn’t exist, Target table name is empty (without Custom SQL), SQL Script is empty (with Custom SQL), the query fails, or the connection is empty, unknown or can’t be opened, the number of errors is 1.

Result files

Passed on unchanged.

Result rows

Without Custom SQL: passed on unchanged. With Custom SQL: Clear list of result rows before execution removes the previous result rows, also when Add rows to result is unchecked. Add rows to result adds the rows the query returns, also when the condition is not met.

Lines read

The number of rows counted.

With Custom SQL, the action counts the rows the query returns, not a value in them. SELECT count(*) FROM my_table returns 1 row, so the number of rows is 1 whatever the table holds. Write a query that returns one row per item to count, such as SELECT * FROM my_table WHERE ….