I’m having trouble with this as well, and I think I have everything correct. In this case, SERIAL is a variable:
"select client_code, asset_no from table where serial_no = '"+SERIAL+"'"
If I execute the same query with an actual string in place of the SERIAL variable, it works, but when I execute this way I get no result.
I had trouble inserting variables directly into the string as well. I’ve been placing the variables in as input parameters instead, then in the string you just insert a ?
So in your example the string would be:
“select client_code, asset_no from table where serial_no = ?”
Then your paramaters would be the value SERIAL with the direction of In
Hi @Dave,
I am trying to achieve something similar - Need to pass a string variable to a query. However, I cannot seem to make it work.
The string var1 has the value “Select distinct(New_Team_Number) from [Sheet1$]”
I want to use var1 in the actual Execute query:
“select column1, column2 FROM table where column1 in [insert var1 here]”
How do I pass var1 to the query? (I tried @, +, & but nothing worked)
I realized my earlier answer is different based on the type of database/connection. My earlier answer with the ‘?’ was for an ODBC connection. For SQL you do use the usual ‘@argument’ in your statement.
Do you have var1 in the input paramaters? Make sure you have var1 in there and no misspellings (I believe it is case sensitive, but can’t remember).
Then type in “select column1, column2 FROM table where column1 in @var1”
If that still isn’t working, try this instead: “select column1, column2 FROM table where column1 in ?”
“Select column1,column2 from tablename where column1=@var1” Try this @oshinkavdia
@Dave @sreekanth That does not seem to work. Maybe because I am passing the excel sheet data directly in var1.
Can I directly pass the datatable (excel output) column1 values in the query?
Also, @Dave, I tried “select column1, column2 FROM table where column1 in ?” and it gives an error ORA-00911: invalid character
Can you share a screenshot of the var1 variable, the sql query, and the input paramaters?
You could pass from the datatable exactly, but it needs to be a single item, not the entire table. So the value in the input paramaters would be something like datatable.rows(index).item(“ColumnName”).ToString with the direction=In and the Type=string
Here’s an example of a query I’m using. PreviousBusinessDay is a datetime variable in the workflow, and I’m passing it as an input parameter in the query
1: Excel data Output in datatable: dt
2: Parameters and query used- (need to pass the entire column from the datatable as a list of values in the query)
ok I understand now. I’ll have to defer to someone a bit more knowledgeable on this aspect of UiPath though. I’m not 100% sure, but don’t think the query can read the UiPath datarows and datatable variables
If it were my own project, I would try converting the columns to an array(of string) and pass that instead. I’m not 100% sure on how to proceed with the @table1 portion though. Just taking a look at your query, I don’t think it’s necessary though? Couldn’t you just say …‘where id1 in (Select Distinct (@col2))’" this would then make sure that id1 is in the array of strings you passed in as @col2
@Dave Yes I can make it work without using @table1. However, I still need to figure out how to pass list of distinct values from column2. Could you please check with someone who can help with this? Appreciate your help!
First give it a try by converting your datacolumn to an array or list of string. This can be done with a for each row activity and an assign activity. Then, join the list/array as a single string separated with a comma like shown here c# - How do I create a comma delimited string from an ArrayList? - Stack Overflow
Then, paste that single string as your in argument.
EDIT: I can’t remember the syntax offhand, so you may need to join the string with a comma and a space
Thanks @Dave. I am able to capture all the values as a string as seen in the below screenshot:
However, when I pass the argument as a parameter in the query I get the below error:
That error shows up when parameters are not properly passed. Make sure to double check that input parameters are properly named (no spaces, case sensitive, etc). Also make sure that you aren’t including too many parenthesis or single quotes since it looks like your variable already contains that.
I’d guess the syntax is somehow off, but without seeing the workflow it is hard to pinpoint.
If you can’t figure out what exactly is throwing the error, one easy way to fix it is by saving the entire SQL statement as a new string and just passing that only, no input paramaters needed. It’s not ideal, but can save some sanity instead of trying to debug syntax issues
Thank you so much @Dave. It worked 
I passed the entire string to the Execute query
Hi sir,
Could you please share me how to do it?
Thank you.
Hi @Dave
Could you please help me with below case.
I get compile error for below query, I am not able to figure out what quotations are causing this.
select * from (SELECT
IsNull(CONVERT(varchar(500), A.newname), ‘’) “User”,
IsNull(CONVERT(varchar(50), B.numbers), ‘’) “Waiting for approval”, IsNull(CONVERT(varchar(50), B.oldest), ‘’) “Oldest claim date1”, IsNull(CONVERT(varchar(50), B.manydays), ‘’) “# of claims over 5 days1”,
IsNull(CONVERT(varchar(50), C.numbers), ‘’) “Returned parts not acceptable”, IsNull(CONVERT(varchar(50), C.oldest), ‘’) “Oldest claim date2”, IsNull(CONVERT(varchar(50), C.manydays), ‘’) “# of claims over 5 days2”,
IsNull(CONVERT(varchar(50), D.numbers), ‘’) “Parts not returned in time”, IsNull(CONVERT(varchar(50), D.oldest), ‘’) “Oldest claim date3”, IsNull(CONVERT(varchar(50), D.manydays), ‘’) “# of claims over 5 days3”,
IsNull(CONVERT(varchar(50), E.numbers), ‘’) “Revised and Re-submitted”, IsNull(CONVERT(varchar(50), E.oldest), ‘’) “Oldest claim date4”, IsNull(CONVERT(varchar(50), E.manydays), ‘’) “# of claims over 5 days4”
FROM
(select co.COMPANY_CODE as companycode, CASE WHEN a.ASSIGNED_TO_EMAIL IS NULL THEN
CONCAT(co.COMPANY_CODE, '-General') ELSE CONCAT(co.COMPANY_CODE, '-', a.ASSIGNED_TO_EMAIL) END newname
FROM claim as c
inner join company as co on c.COMPANY_ID=co.ID
left join claim_assigninfo as cas on c.ID=cas.CLAIM_ID
left join assign_info as a on a.ID=cas.ASSIGN_INFO_ID
WHERE co.COMPANY_CODE IN ('CDA', 'USF') GROUP BY co.COMPANY_CODE, a.ASSIGNED_TO_EMAIL) A
LEFT OUTER JOIN
(select CASE WHEN ai.ASSIGNED_TO_EMAIL IS NULL THEN
CONCAT(co.COMPANY_CODE, ‘-General’) ELSE CONCAT(co.COMPANY_CODE, ‘-’, ai.ASSIGNED_TO_EMAIL) END assignedto,
count() as numbers,
CONVERT(date, MIN(c.CREATED)) oldest,
sum(case when c.CREATED < DATEADD(DAY, -5, GETDATE()) then 1 else 0 end) as manydays
from claim c
inner join company as co on c.COMPANY_ID=co.ID
left join claim_assigninfo as cai on c.ID=cai.CLAIM_ID
left join assign_info as ai on ai.ID=cai.ASSIGN_INFO_ID
where
co.COMPANY_CODE IN (‘USF’, ‘CDA’) and
c.[STATUS] = ‘WAITING_APPROVAL’
group by co.COMPANY_CODE, ai.ASSIGNED_TO_EMAIL) B ON A.newname = B.assignedto
LEFT OUTER JOIN
(select CASE WHEN ai.ASSIGNED_TO_EMAIL IS NULL THEN
CONCAT(co.COMPANY_CODE, ‘-General’) ELSE CONCAT(co.COMPANY_CODE, ‘-’, ai.ASSIGNED_TO_EMAIL) END assignedto,
count() as numbers,
CONVERT(date, MIN(c.CREATED)) oldest,
sum(case when c.CREATED < DATEADD(DAY, -5, GETDATE()) then 1 else 0 end) as manydays
from claim c
inner join company as co on c.COMPANY_ID=co.ID
left join claim_assigninfo as cai on c.ID=cai.CLAIM_ID
left join assign_info as ai on ai.ID=cai.ASSIGN_INFO_ID
where
co.COMPANY_CODE IN (‘USF’, ‘CDA’) and
c.[STATUS] = ‘WAITING_PARTS’
group by co.COMPANY_CODE, ai.ASSIGNED_TO_EMAIL) C ON A.newname = C.assignedto
LEFT OUTER JOIN
(select CASE WHEN ai.ASSIGNED_TO_EMAIL IS NULL THEN
CONCAT(co.COMPANY_CODE, ‘-General’) ELSE CONCAT(co.COMPANY_CODE, ‘-’, ai.ASSIGNED_TO_EMAIL) END assignedto,
count() as numbers,
CONVERT(date, MIN(c.CREATED)) oldest,
sum(case when c.CREATED < DATEADD(DAY, -5, GETDATE()) then 1 else 0 end) as manydays
from claim c
inner join company as co on c.COMPANY_ID=co.ID
left join claim_assigninfo as cai on c.ID=cai.CLAIM_ID
left join assign_info as ai on ai.ID=cai.ASSIGN_INFO_ID
where
co.COMPANY_CODE IN (‘USF’, ‘CDA’) and
c.[STATUS] = ‘WAITING_PARTS’ and
DATEADD(DAY, co.RETURNS_PARTS_DAYS, c.UPDATED) < GETDATE()
group by co.COMPANY_CODE, ai.ASSIGNED_TO_EMAIL) D ON A.newname = D.assignedto
LEFT OUTER JOIN
(select CASE WHEN ai.ASSIGNED_TO_EMAIL IS NULL THEN
CONCAT(co.COMPANY_CODE, ‘-General’) ELSE CONCAT(co.COMPANY_CODE, ‘-’, ai.ASSIGNED_TO_EMAIL) END assignedto,
count() as numbers,
CONVERT(date, MIN(c.CREATED)) oldest,
sum(case when c.CREATED < DATEADD(DAY, -5, GETDATE()) then 1 else 0 end) as manydays
from claim c
inner join company as co on c.COMPANY_ID=co.ID
left join claim_assigninfo as cai on c.ID=cai.CLAIM_ID
left join assign_info as ai on ai.ID=cai.ASSIGN_INFO_ID
where
co.COMPANY_CODE IN (‘USF’, ‘CDA’) and
c.[STATUS] = ‘INFO_REQUIRED’
group by co.COMPANY_CODE, ai.ASSIGNED_TO_EMAIL) E ON A.newname = E.assignedto
) tab
where tab.[user] not in (‘CDA-Andrew.Schmotzer@husqvarnagroup.com’,
‘CDA-eva.hanzalova@kisplus.cz’,
‘CDA-jessem@messicks.com’,
‘CDA-sdoucet@hhgolfcarts.com’,
‘USF-alexandriabuilders@gmail.com’,
‘USF-barsantiandrea2004@gmail.com’,
‘USF-Andrew.Schmotzer@husqvarnagroup.com’,
‘USF-barsantiandrea2004@gmail.com’,
‘USF-bernd.antel@husqvarnagroup.com’,
‘USF-Brendan.Schuler07@gmail.com’,
‘USF-ddurand89@hotmail.com’,
‘USF-eva.hanzalova@kisplus.cz’,
‘USF-huskyshop0452@gmail.com’,
‘USF-ingo.rehm@husqvarnagroup.com’,
‘USF-jessem@messicks.com’,
‘USF-jkpowerequipment@gmail.com’,
‘USF-sdoucet@hhgolfcarts.com’,
‘USF-warranty@oakborotractor.com’,
‘USF-agrolazzarin@hotmail.com’,
‘CDA-Tristan.Bennett@husqvarnagroup.com’,
‘CDA-toppron@yahoo.com’,
‘USF-toppron@yahoo.com’,
‘USF-andrew.conville@husqvarnagroup.com’,
‘CDA-Ivan.Woods@husqvarnagroup.com’,
‘CDA-Jon.Lewis@husqvarnagroup.com’,
‘USF-Michelle.Batchatis@husqvarnagroup.com’,
‘USF-Tristan.Bennett@husqvarnagroup.com’,
‘USF-Jon.Lewis@husqvarnagroup.com’
)