Toad World® Forums

Data Loop with Data Profile task Issue with creating select statement

I'm not sure what I'm doing incorrectly. Thanks for any assistance!

dataloop1.png

The syntax you are using is specifc to creating a table. For sql statements just use select * from #Loop_dataSet.tablename#

just like you did in the output pdf name

It throw me off because it highlights everything green except for the trialing '#'.

Any way of getting the actual value of the variable and not the variable name in the pdf coverpage(profiling summary)?

it should be working as you expect it to. What connection type are you using? Try using a bind symbol instead of literal replacement symbol #var# This would be : for oracle and @ for SQL Server.

I’m using a MS SQL Server connection.

I get the following error when I use ‘@’.

Profile_Data_1 - Exception has been thrown by the target of an invocation.:
Must declare the scalar variable “@Loop_data_1_SQL”.

Can you attach your automation script? This should work fine and I don’t see what the problem is. So viewing the full scrip will help

actually in ms sql you can also use :myVar (with semicolon)… when you do that then the value is replaced by the value from a variable that was set before in some automation script activities (such as SetVariable, LoopDataset activities). I am afraid that when you use @myVar then you are forced to use DECLARE statement in ms sql before you call the select statement and you can’t really bind the automation variable value to the one declared within sql code…

But i am confused in what is the problem here - as Debbie mentioned, if you can clarify where and how is the dataset variable used it would be great.

Martin is correct. I use :variableName inside a sql statement for MS SQL Server all the time. You just have to have a variable with the same name that follows the : with the value set before the sql statement executes.