Test Data Manager

Hi All,

I'm trying to generate a string from the values extracted from a database.

I have this query: 

SELECT TOP 5 ID FROM DBO.TestMart_PROD_ORD WHERE DBLocation='nopcomm' ORDER BY QUANTITYSOLD DESC

Which will bring me the 5 best selling articles from my webstore, but I what to use it as an input for another query so I need them in the format of (1,2,3,4,5). I've tried with the replace formula to indicate that instead of the <CR><LF> uses the ',' but it didn't work because on how the query is responding.

Anyone could have any idea on how to solve this?

Regards,

Felipe A. Hernandez.

I think what you want is something like:

Select * from  ST where ST.ORDER_ID in (SELECT TOP 5 ID FROM DBO.TestMart_PROD_ORD WHERE DBLocation='nopcomm')

Hi Ken, I tried to use the query on this way:

Select * from ST where ST.ORDER_ID in (SELECT TOP 5 ID FROM DBO.TestMart_PROD_ORD WHERE DBLocation='nopcomm' ORDER BY QUANTITYSOLD DESC);

But is failing saying that the ST object does not exist.

ST was just an example table alias. You need to use the correct table and column names for your db.

Maybe you could create a stored procedure at the DB level, where you can loop through the results and keep appending to a string, and then output the required string. In Datamaker, you can use function execsqlproc to run the stored procedure and get the output in a Datamaker variable.

Would this work for your scenario?

Yes that could work, could you give me some pointers on how to create this procedure?

Felipe,

Assuming you are working against MSSQL DB, here is a link that might be a good starting point:

Create a Stored Procedure 

This link explains how to return data from a stored procedure:

Return Data from a Stored Procedure 

For Datamaker, you will find the details of the execsqlproc function here:

Datamaker Functions and Parameters - CA Test Data Manager - 3.6 - CA Technologies Documentation 

Hope these links help - Good luck!

Cheers,

Sameer