Best approach using a query that will fields hundreds of responses

Help Studio
1

Hello, I am working on my first automation in UiPath. I will have a list of strings (idNumbers) that I have to query the data from DB2 and then filter down by exclusion criteria. I could have up to 800 id Numbers. Is the best approach to query the entire list of strings from DB2 and then for each row filter down the criteria? Of is it better to query one by one and then filter ? My one concern is if I query the entire list at once … what if one id number brings back more than one row ? I will have to find a way to combine results for that id number. Thank you so much!! Any input is really appreciated

2

Hi @hannahscott216

Write query to get all ID’s from DB2 and store it in a datatable variable. Then use the bulk add queue item activity to add all ID’s to the queue.

Then you can iterate the queue, one each id considered as one queue item. You can loop through all ID’s. Then not a single I’d will miss.

Hope it helps!!

3

Thank you ! I will definitely try this ! When I loop through all the ids , I am checking about 30 fields for each id… is it best to assign them to a dictionary of string types and then re instantiate it at the beginning of each loop? Also a lot of the checks are for if a field starts with or contains something … is it best to do this with a filter database activity or If statements… most of the outcomes are the same… to exclude based on the criteria. Thank you again !

4

Hi @hannahscott216 ,

UiPath Workflow Example

  1. Add Variables:
  • idNumbers (List of String): Your list of idNumbers.
  • queryResult (DataTable): To store the results from DB2.
  • filteredResult (DataTable): To store the filtered results.
  1. Build SQL Query:
  • Use a String.Join method to build a single SQL query for all idNumbers.
  • Example:
query = "SELECT * FROM YourTable WHERE idNumber IN ('" + String.Join("','", idNumbers) + "')"
  1. Execute SQL Query:
  • Use the Execute Query activity to run the query and store the results in queryResult.
  1. Filter Data:
  • Use a For Each Row activity to iterate through queryResult.
  • Apply your exclusion criteria within the loop.
  • Use an If activity to check and filter the rows based on your criteria.
  • Add the filtered rows to filteredResult.

Regards
Sandy

5

If you have to Filter the data based on the conditions, then I recommend you to use linq expression to filter the data in the datatable (datatable contains all data in the database).

After that you can add the filter data to the queues itself… @hannahscott216

Hope you understand!!