topic Re: Help: Sum counts from 5 Sql statemtns? in Talend Studio

Help: Sum counts from 5 Sql statemtns?

_AnonymousUser — Fri, 16 Mar 2012 19:43:03 GMT

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

Re: Help: Sum counts from 5 Sql statemtns?

Anonymous — Mon, 19 Mar 2012 02:04:13 GMT

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

Re: Help: Sum counts from 5 Sql statemtns?

Anonymous — Mon, 19 Mar 2012 17:55:45 GMT

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.

Re: Help: Sum counts from 5 Sql statemtns?

Anonymous — Tue, 20 Mar 2012 02:21:35 GMT

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