Have a sql server as a source database and a source field is called amt which is a Boolean field.
What tool to use to check if a value for amt > 300000.00 then insert the entire row into another table and add comment to a field called Reason = 'Amount exceeded 300000.00' .
NOTE: Reason field is not coming from source, but it is in the target table.
Your question is a little confusing as you mention your column "amt" is defined as boolean (which should only allow true or false as values) If this is a simple mistake in the post, give the following a try:
you should be able to design a job that would look like this: tDBInput ---row---> tDBOutput
in the DB input query, write something like the following:
"SELECT *, 'Amount Exceeded "+context.THRESHOLD_VALUE+"' as Reason FROM source_table WHERE amt > "+context.THRESHOLD_VALUE+""
This would retrieve only the rows in the source table that have amt greater than your expected value stored in the context variable THRESHOLD_VALUE, and copy them to the target table configured in your db output component.
Firstly, How could a boolean field contain Integer ?
If thats by mistake then you can simply create a Select query with where clause on the amount field checking if its greater than 300000.00 and populate the Reason field of target with " Amount exceeded 300000.00 ".
Additional : If your Source and Target tables are in the same instance of MsSQL then you should go for ELT components ,
you will surely get the performance boost in terms of time taken .
you can either use the filter available in map to filter out the rows or you can process at the column level to pass the value only if it is greater than some specific value.
