Hi,
I got a List of values from sql query. from those values can be inserted in to the below IN LIST_RETURNED_FROM_ABOVE_QUERY )]
This query was different from above and this query is from different database from above query
WHERE F.family_orgcode IN ( LIST_RETURNED_FROM_ABOVE_QUERY )]
Thanks in advance,
Venkat,
Hi Venkat,
I think the way you are getting data from 1 database based on the data from other databse is inefficient. First try to see if you can use the XA_Transaction and join the table between 2 databases?
If that apporach is not doable in your case then you can proceed as you are doing.
To proceed like this, do as follows:
1> query the first data base and get the returnList
2> Add a map step and there init a temp variable tempList with blank.
LOOP (over the returnList)
----Map step (set tempList with %templist%%returnList%,). Make sure the “Perform Variable substitution” Flag is checked.
After this LOOP add code to delete the last comma (which is extra) from returnList.
Now you can use this tempList in yo SQL as select … where IN(%tempList%). Make sure the “Perform Variable substitution” Flag is checked.
HTH,
Bhawesh.
Hi Venkat,
Use the following logic. It is simpler than the previous one I wrote to you:
1> query the first data base and get the returnList
2> LOOP over returnList to create a stringList.
3> invoke the service pub.string:makeString and supply the stringList to elementList and set the separator to ,. This service will give you value as output.
4> Now you can use this value ito prepare your SQL as select … where… IN (%value%). Make sure the “Perform Variable substitution” Flag is checked.
CAUTION: some DB limits the no. of values in the IN section of SQL.
THT,
Bhawesh.