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

If I write "... where id in @tx_id" the error is "MySQL: Fatal error encountered during command execution."

 

Otherwise

 

the error is "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@'tx_id'' at line 13

 

I suppose it is a syntax error because when I replace the condition by "where id in (1,2)" I obtain results I am expecting.

 

I have also tried to modify the query directly in the Advanced Editor : 

"let

dbQuery="select ... from ... where id in" & @tx_id,

Source = MySQL.Database("server", "name_database", [Query=dbQuery, CommandTimeout=#duration(0, 16, 40, 0), ReturnSingleDatabase=true])
in
Source"

 

but I have no result.

mmanwaring's avatar
mmanwaring
Resolver I
9 years ago

Hello Alicia

I have got this to work by concatenating my parameter into the SQL

for example

 

where id = ' "& Parameter &" '

just be sure to drill down in power query so that your parameter is a single value. if you need multiple id then this will be different to create.

thanks

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

      • alicia57's avatar
        alicia57
        Helper I
        9 years ago

        Hi Mike, 

         

        Thank you again for your answer but I want to automate all the work in Power BI without using Excel or another software.

         

        I have a first query launched in Power BI under a SQL Server database. From this result I have selected the column which I want to use in another query, convert into number (because in the other database on which I will launch the other query, this field is number type) and I click on "Add as New Query", then I have created my parameter "tx_id".

         

        My second query is launch on a MySQL database. In the advanced editor, I tried WHERE id IN ("& tx_id &") but I have the error message : "DataSource.Error: MySQL: This database function doesn't support the query option 'Query' with value 'null'." whereas I have values in my list.

         

        Regards,

        Alicia