row count from two files

[shalinim1]

Hi,

In my job, I am getting the input from database (MySQL) table. I want to get the row count of two input tables and want to compare the count. If the count id not equal, then the mail has to be triggered with the missing values as subject. Could anyone help me with the procedure?

[Anonymous]

Hi

Use a tMysqlInput to execute a count query, and store the count to context variable for comparison later, for example:

tMysqlinput1--tJavaRow1

|onsubjobok

tMysqlinput2--tJavaRow2

|onsubjobok

tJava--runIf--tSendmail

on tJavaRow1:

context.count1=input_row.count;

on tJavaRow2:

context.count2=input_row.count;

on tJava:

if(context.count1==context.count2){

context.sendMail=false;

}else{

context.sendMail=true;

}

conext.count1, context count2 are context variables, int type.

context.sendMail, context variable, boolean type.

Set the condition of runif as: context.sendMail

Hope it helps you!

Regards

Shong

[manodwhb]

@Shalini M​ , you take the two count queries and use in tDBInputs and the connect to tmap and then do inner join and take the innerjoin rejects to output in tMap and connect to tSendemail compontn to trigger email.

Thanks,

Manohar

[cadap]

do you really need the count or only the differing id values?

if you need the differing values, you can perform this with a single sql query.

In MSSQL one would do this with the EXCEPT keyword.

But in MySQL you have to use Joins or Subselects.

This page explains it well:

http://www.geeksengine.com/database/multiple-table-select/minus-except.php

[shalinim1]

Thank you will check on it. I need the count of missing values and that count should be included as Subject in the mail we are triggering!

[cadap]

For the count of the missing values, just get the missing values with the sql query and put the result to a count(*).

This can all be done in one query.

For two tables, table_1 and table_2, finding all missing values in table_2 with an arbitrary ID and count them:

SELECT count(t1.ID) FROM table_1 AS t1

LEFT JOIN table_2 AS t2 ON

t1.ID=t2.ID

WHERE t2.ID IS NULL;

If you want to find all values missing in table_1, just interchange the table names in the query.

[shalinim1]

getting row count of a table is okay. how to make it as the subject of mail? When I tried with a java code. it is sending mail with the entire code as subject.

[DataTeam1]

@Shalini M​  you need to use your context value in the subject field

[shalinim1]

Thank you. Will check on it.