Toad World® Forums

Issues with Automation Loop in 3.8 Data Point Pro

Hi, I’m using 3.8.1.677 Toad for Data Point Pro.

I’m just starting to get into variables & looping so this could just be an easy answer.

I’m trying to run a simple query in a SQL server DB:

select TRN_SK from TRN_SK_TABLE;

*Note - TRN_SK is a PK

It returns 3 unique rows of TRN_SK values.

I want to pass these 3 values into the following statement in a separate Oracle database:

select TRN_SK, TRN_FTR, TRN_CD, TRN_TYPE from ABC_TRN where ABC_TRN.TRN_SK =

*Note - TRN_SK is a PK

(Essentially, I want the statement above to behave like an IN list & pass in the 3 values found in the initial SQL Server statement.)

Then, return the 3 rows of results in an Editor window in the SAME tab.

Hope this all makes sense. TIA!

What issue are you having? Could you post a screenshot and give some description?

It seems you can use Loop Dataset activity in automation, please refer to the following blog

www.toadworld.com/…/automation-variables-automation-variables-using-datasets

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?
loop.jpeg

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)

IF OBJECT_ID (‘tempdb…#DataTable’) IS NOT NULL

DROP TABLE #DataTable

Select *

into #DataTable

from

(Select 1 GrpColumn, ‘601’ TRN_SK union all

Select 1 GrpColumn, ‘602’ TRN_SK union all

Select 1 GrpColumn, ‘603’ TRN_SK) D

select GrpColumn, STUFF((select ‘,’ + ltrim(str(TRN_SK))

from #DataTable For XML Path(’’)), 1, 1, ‘’) TRNs --TRNs is the field name you will use later.

from

#DataTable

Group by GrpColumn

Then use a Set Variable Value (not to be confused with Set Variable) and concat(’#CmdString#’, ‘#String4In.TRNs#’)

Use the Set Variable Value to concatonate a final ‘)’ just like above and now you can Execute (:CmdString) to editor, excel or any text file.

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)

What do you think guys?

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.