Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Using a list as a parameter of an SQL query

Hello. I'm not sure how to use a list as a parameter of an SQL query. The list I have is of integers: I'm trying to use it as a "WHERE IN" parameter inside an SQL query through the advanced e...
Need Help
  • Vijay_A_Verma's avatar
    Vijay_A_Verma
    4 years ago

    You are just there. Just replace ProtocolosEquipe10 by following

    Text.Combine(ProtocolosEquipe10,", ")

    let
    Fonte = Sql.Database("test", "test_readonly", [Query="SELECT *#(lf)FROM TEST tst#(lf)WHERE tst.field IN (" & Text.Combine(ProtocolosEquipe10,", ") & ")"])
in
    Fonte
Vijay_A_Verma's avatar
Vijay_A_Verma
Most Valuable Professional
4 years ago

You are just there. Just replace ProtocolosEquipe10 by following

Text.Combine(ProtocolosEquipe10,", ")

let
    Fonte = Sql.Database("test", "test_readonly", [Query="SELECT *#(lf)FROM TEST tst#(lf)WHERE tst.field IN (" & Text.Combine(ProtocolosEquipe10,", ") & ")"])
in
    Fonte
  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi, Vijay_A_Verma! Thanks for your reply.

    I got this error, right on the first iteration of the list:

     

    Expression.Error: We were unable to convert the value 9810656 to type Text.
Details:
     Value=9810656
     Type=[Type]

     

    EDIT:

    I converted my data type to text before converting it to list and I was able to perform the query. I don't know if it's the most appropriate way, but it worked.