Forum Discussion
Use a parameter in a query in a MySQL database
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
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
Finally I have decomposed my query into several queries but I have still an error:
"Formula.Firewall: Query 'Query1' (step 'Source') references other queries or steps, so it may not directly access a data source. Please rebuild this data combination."
Regards,
Alicia
Yes mine always asked to run the native query, i think that can be turned off in options. it looks like you are close to solving this.
How many id's are you passing into the WHERE clause?
Mike
I have more than 100000 id but as I said in the previous post, I have split the result of the query and now it's fine.
Now I have the problem about the "data combination". After some researches on the net, it seems we can not combine an external data source with another query.
let
Source = Sql.Database("host", "database", [Query="select * from ...'", CommandTimeout=#duration(0, 16, 40, 0)]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"tx_id", type text}}),
tx_id = Text.Combine(#"Changed Type"[tx_id],"','")
in
tx_idAfter this step I obtained the list of id.
Then I want to launch my second query taking into account the obtained list of id:
= MySQL.Database(Server,Database,[Query="select ... from ... where id in ("& tx_id &")", CommandTimeout=#duration(0, 16, 40, 0), ReturnSingleDatabase=true])
I have the error here whereas the query in which I used the list "tx_id" is in another editor.
Regards,
Alicia
Hi Alicia
Perhaps there is another way, you could put the whole SELECT clause for the ID's into the WHERE clause on the other query
= MySQL.Database(Server,Database,[Query="select ... from ... where id in (select id from mytable )", CommandTimeout=#duration(0, 16, 40, 0), ReturnSingleDatabase=true])
Mike
Hi Mike,
Thank you for your suggestion but the two queries are not launched on the same database.
Edit : Finally it was a problem of options, I have to select "Always ignore Privacy Level Settings" in Options -> Options and settings -> Privacy
Thank you for your help !!
Alicia