Help with using a variable as IN clause in SQL

[Yomi_Oluwadara (darkNight)]

I was able to execute a SQLquery (in_arg with type string) whenever I pass in the IDs directly into the in_arg as below

"Select * from abc
where  abc.colname in (12,12,344)

in_arg declaration.

For business reasons, I do need to be able to pass the values of ‘abc.colname’ as a variable, so the query then looks like this below:

“Select * from abc
where abc.colname in +stringAcctIDs”

stringAcctIDs was declared as below:

When I passed in the variable into the SQL query as shown below

I got the error below:

I have declared the variable stringAcctIDs as String, an array of String , but it has not worked.
How can I make the SQL work with using the stringAcctIDs ?

[adiijaiin (Aditya Jain)]

@Yomi_Oluwadara

“Select * from abc
where abc.colname in +stringAcctIDs”

The query should be like:

"Select * from abc where abc.colname in "+stringAcctIDs+ "

The plus should be out of double quotes. Your variable is being passed as a normal string strArray and not like a variable.

You can also do it like
Create a query variable of string type:
assign the above expression first to that query variable and then pass it on in your activity.

Thanks

Happy Automation!

[Yomi_Oluwadara (darkNight)]

@adiijaiin
Thanks but that did not work. Actually my IN clause is within other qualifications like below

"
select * …
WHERE t1.c = ‘a’ AND t2.c2 = 44522223 AND table.col IN +stringAcctIDs and t4.c4 = 1
"

[adiijaiin (Aditya Jain)]

hi @Yomi_Oluwadara

The only way is to use a variable first, you can call it varQuery or anything

Construct the query over there and then review it by placing a breakpoint and debugging the file.

Thanks

[Yomi_Oluwadara (darkNight)]

The Query was initially stored in the in _arg

the in_arg was updated with the var ( holding the IDS) in the assign activity before executing the updated query

[postwick (Paul Ostwick)]

Don’t break your query across multiple lines.

[Yomi_Oluwadara (darkNight)]

@postwick , thanks for the input.
What if the query is like 100 lines?

[Anil_G (Anil Gorthi)]

@Yomi_Oluwadara

Please try like this

In the query variable use value as

"Select * from abc where abc.colname in (<IDVALUE>)"

In the id values variable use value as you already did as an array

Now in the run query command give in_querytoExecute.Replace("<IDVALUE>",String.Join(",",StringAccountIDS.Select(function(x) x.ToString)))

Hope this helps

Cheers

[Yomi_Oluwadara (darkNight)]

@Anil_G
Thanks, I updated my qiery variable as shown below

I also updated the run query as shown below

However, I ended up with this error:

Not sure if its a spacing issue

[Yomi_Oluwadara (darkNight)]

it worked when I changed the type from String to String array