Search code examples
sql-server-2008sqlitepentahokettlepentaho-spoon

Comparing rows count for two different tables Pentaho

I want to compare two row counts for two tables from two different connexions.
I've tried to get the number of rows for each different table by executing
Select count(*) as count1 from Table1
and
Select count(*) as count2 from Table2
in two different Execute SQL Script steps, as in the following screenshot, but I have no idea how to proceed.
enter image description here Especially, I want to get the two different counts and compare them then branch with success/failure on whether they're equal or not, respectively.
How can I achieve this ?

Solution

  • It's pretty easy. There is a step called Evaluate rows number in a table. This both gets a row count from a table and tests it against a value. The value can come from a variable in the Job (note Job, not Transform).

    So all you need to do is create a variable with a Set variables task, get the row count from one of your tables and then execute the Evaluate rows task. The following job will do just that.

    Count Compare

    The transform to get the row count for the other table is very simple. Just execute a SELECT COUNT(*) FROM {tblname} in a Table input step and flow the output to a Set variables step in a transform. Be sure to mark the variable as valid in the parent job.

    Set Row Counts

    You can also execute an SQL against a connection with the JavaScript step which would avoid creating the transform, but I prefer to avoid scripting when possible.