Are you trying to pass the 3 TRN_SK values, one at a time using the loop or all 3 at once? All 3 at once would require an In instead of =, and I am not sure if Toad does multivalue variables that way (like SSRS does). You can build dynamic sql using the loop to make the In statement from the 3 values and then execute the dynamic sql statement stored in a text variable. Not sure what you are actually trying to do.
Hi, yes, that is what I’m trying to do. Pass in the 3 returned values via an “IN” list. Then, get the resulting 3 records returned all put in the same tab in an editor instance. Right now it is just executing 3 times and each row returned is being put in a separate tabs (total of 3 tabs) in an editor instance. How would I build dynamic sql using the loop to make the In statement from the 3 values and then execute the dynamic sql statement stored in a text variable?
You don’t nee a loop for what you are doing. Try this:
Create a variable (Set Variable) (call it CmdString or whatever you want to name it) type string and make its value Select TRN_SK From YourTable Where TRN_SK in (
Then create a 2nd variable (call it String4In) type SQL put in SQL code with a stuff function like below (you don’t need to create a #DataTable just use your real table)
I believe that what Greg suggests is mssql specific, because ‘for xml path’ is not available in mysql (for example).
What you can do is to simply insert those PK column’s values into a temp table that you can inner join later which should be the same effect… without using any sort of variables… If you need to persist that table over connection then try use ##myTable format (double hash) - they will not be droped once connection is changed and will be accessible over to other users too…
So to be more generic, i’ve tried to do this using expressions in the Set variable activities and i can’t manage to do some easy-to-understand workaround, because the single/double quotes are being stripped off for some odd reasons… I consider this as a wrong way how ‘string’ values are handled in the expression engine - an opportunity for improvement for the future…
But even if i’d succeed in this then it will still be “ugly” solution… i was thinking of some more elegant way like just passding the #mySqlVariable.PKColumn" but this feature only takes the “current” row (current row from the loop dataset activity)… I think we should expand this feature and let the user to decide which row to use…
My idea is something like this:
Select * from myTable where pkColumn in #mySqlVar# ---- first row and first column
Select * from myTable where pkColumn in #mySqlVar.PKColumn# ---- current row of a specific column (in loop dataset activity)
Select * from myTable where pkColumn in #mySqlVar.PKColumn[6 ]# ---- 7th row of a specific column (indexed from 0)
Select * from myTable where pkColumn in #mySqlVar.PKColumn[* ]# ---- all rows of a specific column (transposed into ‘a’, ‘b’, ‘c’ … or 1, 2, 3 (for numeric types))
Select * from myTable where pkColumn in #mySqlVar[6 ]# ---- 7th row of the first column
Select * from myTable where pkColumn in #mySqlVar[* ]# ---- all rows and first column (transposed)
True, STUFF is a MS SQL Server solution. In Oracle it is easier use ListAgg instead of STUFF (no For XML needed). I have no idea what the MySQL equivalent would be. Look into the Group_Concat funtion for mySQL.
