Filter Source Records using DBQuery

Situation :

We have some data present in the Database table on basis of which we want to filter out the records of Incoming Source file. To achieve this we have created a Db Query that takes value from Source record and verifies the condition.

DB Query: DbQuery { Select FirstName from tablename where LastName = '/Field2' , $DbConnInfo , true}

Example :

DB Table

UniqueID FirstName LastName Age
1 AA BB 21
2 PP QQ 22
3 XX YY 23

Source Record

Field1 Field2 Field3 Field4
00 BB xyz m
01 DD pqr n
02 YY abc o

Target Record (Filtering Data on basis of LastName)

Field1 Field2 Field3 Field4
00 AA xyz m
02 XX abc o


and verifies the condition but we are unable to get the desired results.

Solution:

In order to achieve the desired output, you need to follow the below steps :-

  • Create a local variable for Dbquery at /Root level i.e. $DbQueryRecord with below Query.

DbQuery { Select FirstName from tablename where LastName = '/Field2' , $DbConnInfo , true}

  • Place the Xpath of the Source Record (/Record) in For-each at /Root level.
  • Now, use the variable defined in step1 ($DbQueryRecord) within IFF condition in textual rule at /Record level. As shown below :

         "  IFF {$DbQueryRecord > 0 } VALUES ['true'] "

After following the above-mentioned steps, the desired output will be achieved.

Have more questions? Submit a request

0 Comments

Article is closed for comments.