How to query a data table value?

Help Activities
, , , , , ,
1

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
A-123
B-123
C-123
’
It seems like I need to iterate the string variable but somehow I got stucked. Any ideas how to solve this?

1 Like
2

Hi @Rhys18,

Is there an example dataset?

Regards,
MY

3

Hi

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’

Or

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

4

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

5

Hi,

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

6

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
"))).copytodatatable()

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

dt=dt.AsEnumerable.take(N).copytodatatable()

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:

list_custNo=CustVar.AsEnumerable.select(Function(x) x("customer_number").tostring).tolist()
dt=dt.AsEnumerable.where(Function(a) list_custNo.contains(a("cust_no").tostring)).copytodatatable()
7

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 :

ColumnName
A-123
B-123
C-123
etc

  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?

8

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"):
   str_query=str_query.substring(0,str_query.length-2)

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

9

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

10

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
A-123
B-123
etc

12

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