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.
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
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.
