Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
@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
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
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!
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.
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.
@Shalini M you need to use your context value in the subject field
Thank you. Will check on it.