Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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 Parameter" : my parameter is the list of id named "tx_id".
Now I would like to launch a query on another database (MySQL this time) using this parameter.
select * from...
where id = #'tx_id'
I tried this query with and without quotes, with and without double quotes, with @ and $ instead of # but I always have an error.
Would you have an idea?
Thank you,
Alicia
Solved! Go to Solution.
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 alicia, help-me. I want to create a query in mysql and i dont know how create
if I select mysql, I put the IP and user, and I see tables, but I what create a query thats use many tables .
can you help me?
Humberto Buzzi
what is your error?
Proud to be a Super User!
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.
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
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
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
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
Did you use Text.Combine to convert the query into the format SQL requires?
No, in my Advanced Editor I have only
let
Source = MySQL.Database("server", "name_database", [Query="select ... from ... where id in '("& tx_id &")' " CommandTimeout=#duration(0, 16, 40, 0), ReturnSingleDatabase=true])
in
Source
I don't understand how and why using Text.Combine as it is already considered as a query.
Thanks,
Alicia
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_id
After 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
Nobody would have an idea ?
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
85 | |
83 | |
66 | |
60 | |
58 |
User | Count |
---|---|
194 | |
111 | |
105 | |
79 | |
71 |