How to query a data table value?

Hi, I have a task as described below :

I have a Customer Unique Number by SQL query with date range as parameter
(Example from 1 May 2022 to 2 May 2022, I have 3 customer unique number which is A-123, B-123, and C-123)

now I need to get the value inside the customer number (Name, Phone Number etc). the value of the unique number is in data table and already converted into a String Variable. When I try to assign a query inside a for each loop, my expectation is like this :

select * from table_X where customer_number = ‘A-123’
select * from table_X where customer_number = ‘B-123’
select * from table_X where customer_number = ‘C-123’

Reality this is the writeline :

select * from table_X where customer_number = 'CustomerNumber

It seems like I need to iterate the string variable but somehow I got stucked. Any ideas how to solve this?

1 Like

Hi @Rhys18,

Is there an example dataset?



There are two ways to do this

  1. First we can try with AND operator in the Select statement

select * from table_X where customer_number = ‘A-123’ AND customer_number = ‘B-123’’ AND customer_number = ‘C-123’


If the above one doesn’t work
Just use select * from table_X
And get the datatable as output as dt

-now use a simple linq query to handle this

In a assign activity

dt = dt.AsEnumerable().Where(Function(a) a.Field(of String)(“yourcolumnname”).ToString.Equals(“A-123”) AND a.Field(of String)(“yourcolumnname”).ToString.Equals(“B-123”) AND a.Field(of String)(“yourcolumnname”).ToString.Equals(“C-123”)).CopyToDatatable()

This will do the same you expect

Cheers @Rhys18

Hi, Thanks for replying.

So what if I change the case to getting Customer Number within 2 years range?

Example range : 20/5/2020 - 20/5/2022
Total Customer Number : 1000

How do I process this 1000 contract? What I understand from your reply you assign the customer number manually and not using a variable. Can I assign it with variable like this?

dt = dt.AsEnumerable().Where(Function(a) a.Field(of String (“yourcolumnname”).ToString.Equals(“CustVar”).CopyToDatatable()

CustVar is DataTable variable type


unfortunately I can’t give you the dataset example because it’s very sensitive company data. Thanks for replying btw

say the date column name is “date_test”
then inorder to filter the tables where “date_test” is in range : 20/5/2020-20/5/2022 use this:

dt=dt.AsEnumerable().where(Function(a) (Convert.todatetime(a("date_test").tostring)>=Convert.todatetime("20/5/2020
")) AND (Convert.todatetime(a("date_test").tostring)<=Convert.todatetime("20/5/2022

and to take first N number of rows [N is integer]:


And say you are having a datatable(CustVar) which contains the “customer_numbers” as a column , then inorder to filter out the main table based on only the customers present in the CustVar table you can first convert the customer_numbers inside the CustVar table into a list , then you can use a condition like , list.contains(dt(“cust_no”)) , for eg: x("customer_number").tostring).tolist()
dt=dt.AsEnumerable.where(Function(a) list_custNo.contains(a("cust_no").tostring)).copytodatatable()

Tbh I tried to do your solution but still got confused with it. I will tell u the scenario :

  1. I queried a customer number from MySQL (Example : Select * from table_X where date 2020-2022)

  2. Example result of queries are like below :


  1. I want to select * each data of customer_number

step 1 and 2 alrd done. I’m stuck on step 3. I have a solution in my mind, can I pass all those customer_number into excel, and then I queried using for each data table to make it easier? when the process is done, the excel file will be deleted using delete file activity. Is it an effective solution?

ok so the third step is to make a new SQL query in which you filter out records of all the customer_number that you got in the step2 , right?
lets say you you got a datatable dt_cust_no from step 2 that has the column “customer_number”
And you want to create the SQL query something like this

"select * from table_x where customer_number='A-123' OR customer_number='B-123' OR customer_number='C-123' "

So we need to create this query dynamically, try this (not required to export to excel, work with the datatable):

str_query="select * from table_x where "
for each row in dt_cust_no:
    str_query=str_query+"customer_number='"+row("customer_number).tostring+"' OR "

if str_query.endswith("OR"):

here we dynamically created the SQL query and there is a if case added because there can be one extre “OR” at ending
so you will get str_query same as the SQL query given in above example , and pass it to the query datatable activity , and it will extract data for all the customer_numbers

And 1 more question. I got an insurance end date of a customer and I have to remind them the insurance is almost expired. The reminder is set 3 months before it ends. The output in excel is : Insurance will be expired in 3 months.

Can I execute with this logic :

if(yearValue_EndDate = currentYear) and (currentMonth+3 = monthValue_EndDate)
Assign Var Reminder = "Insurance will expired in “+monthValue+” months

else if (yearValue_EndDate != currentYear) and (currenMonth+3 != monthValue_EndDate)
Assign Var Reminder = "Insurance will expired in “+monthValue+” months

Assigned var write to data table

Thanks, I will let you know is it working or not in the future. But why do I have to use OR in the query? Can’t I just pass all the contract number to a variable and then loop it? Or uipath aren’t able to execute it? and from the query u’re giving, is the ColumnName will be inside the str_query variable?

when I tried write line my code, it’s showing the header and the value like this

Customer Number : Contract_Number

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.