Handling multiple Select fields in DB lookups

Question:

Assume I have a Data Mapping defined with a single Source schema and a single Target schema.

Assume the Source schema has multiple records.

Is there a mechanism for me to do the following:

1) For-each record in Source

2) DBQuery (select fieldA, fieldB, fieldC from db.table WHERE field_pkey=sourceField, $DBConn, True/False)

3) When defining a mapping rule for a target field, is there a way to refer to the data record from step 2 above? Say I want to get the value for ‘fieldB.’

Currently, one option to accomplish this is to create a local variable for each query field:

varFieldA = DBQuery or Select method

varFieldB = DBQuery or Select method

varFieldC = DBQuery or Select method

My concern w/ my approach is that it will execute 3 separate DB queries rather than a single query.

--------------------------

Solution:

Follow the following steps to map separate fields coming from the output of DBQuery:

1. On the target record element (the element where you have applied for-each) create a local variable (say varQuery) with value
DBQuery (select fieldA, fieldB, fieldC from db.table WHERE field_pkey=sourceField, $DBConn, true)

Please make sure that last parameter to this query is true as you are trying to fetch multiple values (fieldA, fieldB, fieldC) from the Query.

2. On the target element where you need to map the fieldA from the above query , apply the mapping $varQuery//Root/Record/fieldA . Similarly apply the mapping $varQuery//Root/Record/fieldB and $varQuery//Root/Record/fieldC to other corresponding target elements. Here varQuery is the name of variable created in step 1.

Here is the brief explanation of the above two steps:

Whenever a DBQuery (with last parameter as true) is executed it returns a output XML with the following structure:

Root

Record

Column Name Column Data /Column Name
---

Column Name Column Data /Column Name

/Record

/Root

In your case when step 1 is executed the variable varQuery will hold the following output

Root

Record

fieldA fieldA Data /fieldA
fieldBf fieldB Data /fieldB
fieldC fieldC Data /fieldC

/Record

/Root

In step two we have applied the mapping as $varQuery//Root/Record/fieldA When step 2 executes, it will go to the variable varQuery and picks the value of fieldA present inside Record. Similarly the other two mappings will be executed.

----------------------

The 3rd parameter in a DBQuery gives the idea of the type of query used in DBQuery Function. If you are fetching multiple rows\columns then it must be set to true and if you are fetching only single column (e.g. select empname from employees) then it should be set to false. The reason I am saying this is because the approach of using DBQuery when 3rd parameter is true is altogether different from when 3rd parameter is false. When 3rd parameter is false then output of DBQuery directly contains the string value of the result. So you can directly map the DBQuery to corresponding target element.

In general guidelines to use the DBQuery function are:

1. If the 3rd parameter is false , then always directly map the DBQuery to the target element or the local variable.

2. If the 3rd parameter is true , then you must iterate over the xml returned by the DBQuery e.g. $varQuery//Root/Record/empname. Here varQuery is the name of the variable holding the DBQuery function.

-----------------------

Question:

How does the system behave if a DB Query returns 0 records and subsequent logic is based on the resulting data set. For WHEN conditions, it seems to always go to the OTHERWISE condition.

Do you have a recommended best practice for checking for recordset results? Should we check the number of records returned?

Response:

1. 3rd parameter to the DBQuery function is false : In this case you can check if the output of the DBQuery function is empty or not using IF or WHEN conditions. E.g.

WHEN CONDITION{$varQuery = '' } VALUE=['Output is empty' ] OTHERWISE VALUE=[$varQuery ] . Here varQuery is the name of the variable holding the DBQuery function.

2. 3rd parameter to the DBQuery function is true : In this case you can do the following:

2.a Either check the number of records returned using count function E.g. count($varQuery//Root/Record)

2.b Or you can use the for-each property on varQuery E.g. $varQuery//Root/Record . Here varQuery is the name of the variable holding the DBQuery function.

If you go by 2.b approach then subsequent logic inside for-each will only run if the output of the query is not empty (i.e. record count >0)