One Star

Help: Sum counts from 5 Sql statemtns?

My project has multiple databases where I run similar SQL commands.
I created a job to run an update statement on these many databases.
Now I'd like to create a QA check to count the numer of rows affected by my job.
Here's my problem:
I have a tMap create the SQL I'd like to run, then I run the command on the database with a tMySqlRow
tMap_2 -> tMySqlRow -> tLogRow
Output:
select count(*) from db1.table where col = 1;
select count(*) from db2.table where col = 1;
select count(*) from db3.table where col = 1;
I can't figure out how to "agregate" or "iterate" or etc the data in my project to sum the results and compare with a number I provide.

Thanks for looking
-Joe
3 REPLIES
One Star

Re: Help: Sum counts from 5 Sql statemtns?

Hi Joe
You can create part of your job like this.
---Iterate-->tMySqlInput-->tJavaRow
Set the schema of tMysqlInput as Image 1.
Set the Query of tMysqlInput like this.
"select count(id) from "+context.TableName+" where col = 1"
Left "Table Name" as "".
Then type these code in tJavaRow.
Context.sum+=input_row.sum;
The default value of Context.sum should be 0.
If you have any question, please let me know.
Regards,
Pedro
One Star

Re: Help: Sum counts from 5 Sql statemtns?

Thank you for the reply Pedro.
I created a FlowToIterate object pointing to the tMySQLInput.
In my picture, out1.sql_command has five results, each are the sql count(*) I'd like to run.
As you can see in my picture, I've added the tJavaRow with code you provided to sum the resutls from the sql command.
I'm not certain it is working--when I run the project, the iterate command shows 2 execs finished. I expect 5.
One Star

Re: Help: Sum counts from 5 Sql statemtns?

Hi
I don't know why it doesn't work. Maybe this is due to out1.sql_command.
Could you show me the five sql queries?
Regards,
Pedro