How to write Sql query into uipath

Help Activities
, , , ,
1

Hi All,

How can I convert below sql query in UiPath suitable syntax.

Select format (dt.[trade date],“mm/dd/yyyy”) as [trade date], sum(dt.[ext fee]) as [ext fee] , dt.[billing no] as [Bill No] from dt
Group by Format (dt.[trade date],“mm/dd/yyyy”),dt.account,dt.symbol

Kindly note that I don’t have office installed in my machine so can’t use Excel as a database.

I want to convert above query using select statement or any other option

Forum Engagement Daily Reports
2

Hi @TUSHAR_DIWASE,

You can refer below forum post first and then the youtube link provided:

  1. Linq query for groupby and sum - #3 by GBK for selecting specific columns and use aggregate functions
  2. https://youtu.be/aRLUDYKbm8M for group by multiple columns.
4

Hi @TUSHAR_DIWASE ,

Can you give your input and output sample. we can use LINQ query on this case.

Thanks

5

Hi @TUSHAR_DIWASE ,

Please see into the code and below result if it looks giving you the expected result.

(From dRow In dtInput.AsEnumerable
Group dRow By formatDate =CDate(dRow("trade Date")).ToString, account = dRow("account").tostring, symbol = dRow("symbol").tostring Into grp = Group
Select dtOutput.Rows.Add({ CDate(grp.AsEnumerable.First()("trade Date")).ToString("MM/dd/yyyy"), 
	grp.AsEnumerable.Sum(Function(row) If( Int32.TryParse(row("ext fee").Tostring,0), 
			CInt( row("ext fee").ToString),
			0) ).ToString,
	grp.AsEnumerable.First()("billing no").ToString})
).CopyToDataTable

Input:
image

Output:

image

Attached the xaml.
SQLQueryToLinq.xaml (12.0 KB)

Post here for your any issue.

Cheers

1 Like
6

@TUSHAR_DIWASE

I hope this is what you are looking for

Use build datatable to build a table with 3 columns with string types…for date,fee and billno. And name datatable as newdt

Let the data in which orginal is present as dt

Now use assign with below

newdt = Dt.AsEnumerable.GroupBy(function(x) x("Trade date").ToString+x("Account").ToString + x("Symbol").ToString).Select(function(x) newdt.LoadDataRow({x(0)("Trade date").ToString,x.Sum(function(y) cdbl(y("fee").ToString)).ToString,x(0)("Bill no.").ToString},False)).CopyToDataTable

Cheers

1 Like
7

@Anil_G thank you so much for the response.

1 Like
8

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