Forum Discussion

alicia57's avatar
alicia57
Helper I
9 years ago
Solved

Use a parameter in a query in a MySQL database

Hello,   I would like to use a list of results from a first query in a second one.   I launch a first query on a SQL Server database. I click on a column "id" and "Add as New Query", then "New Pa...
Need Help
  • mmanwaring's avatar
    mmanwaring
    9 years ago

    Hi

    because tx_id is a series of id numbers you need to pass them into SQL in a format it expects.

    I used Text.Combine to create my parameter so it read '123','124','125' which is what i needed to pass to the SQL where clause. As you are using a number then i do not think you need the '' so you need to pass into SQL IN clause (123,123,123,123) etc if tx_id looks like 123,123,123,123 then you can concatenate it into your SQL like your example.

    So the key to solving this is to make sure that tx_id results look exactly what SQL requires.

    Regards

    Mike

alicia57's avatar
alicia57
Helper I
9 years ago

Hello mmanwaring,

 

Thank you very much for your answer !

 

Indeed I need a list of id. Do you know what is the way to create it ?

 

Regards,

Alicia

mmanwaring's avatar
mmanwaring
Resolver I
9 years ago

Hi Alicia

With SQL you need WHERE id IN ('id','id','id')  for example

so how i got this to work was i created a table in excel which was a single column of values. I then queried that into power query and made the column text, this created the step #"Changed Type" by default.

then add a custom column and turned it into a list. here is the exact code, Text.Combine allowed me to add the ' '  to each value

let
    Source = Excel.CurrentWorkbook(){[Name="CustomerList"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerCode", type text}}),
    CustomerCode = "'"&Text.Combine(#"Changed Type"[CustomerCode],"','")&"'"
in
    CustomerCode

 

then SQL is WHERE id IN ("& CustomCode &")

Regards

Mike