Create string of unique column value for SQL data pull

[#1]

I am using JSL to pull data from SQL. In a Where statement I am defining ShopOrderID manually. How can I make a string(List) of all unique value from different JMP table and use that as a variable to pull data from SQL table? 

I tried summarize function but it did not worked.

[#2]

Use summarize to create an list of items and then concat items to build it into string sql can understand.

For example if you can use IN 

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

Summarize(dt, uniq = By(:age));

sql_in = "('" || Concat Items(uniq, "', '") || "')"; // "('12', '13', '14', '15', '16', '17')"

[#3]

Thanks Jarmo,

What am I doing wrong?

[#4]

You have to evaluate it inside your string

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

Summarize(dt, uniq = By(:age));

sql_in = "('" || Concat Items(uniq, "', '") || "')"; // "('12', '13', '14', '15', '16', '17')"


sql_template = "\[
	SELECT *
	FROM *
	WHERE
		col IN ¤sql_in¤
]\";

sql_str = Eval Insert(sql_template, "¤");

show(sql_str);

I use "¤" instead of relying on the default "^" as it might appear from time to time in some regex patterns.

[#5]

Thanks Jarmo,

I used the script you suggested. It is storing order number in sql_in. But SQL query is not pulling any data.

I confirmed that SQL is working fine when I use WHERE (a.ShopOrderId = '12345') to pull data.

What am I doing wrong. 

Thanks in advance 

[#6]

Somehow script was not attached to my last message.

Names Default To Here(1);
dt = current data table();

Summarize(dt, uniq = By(:OrderNumber));

sql_in = "('" || Concat Items(uniq, "', '") || "')";//"('1','2','3','4','5')"

/********************Connection Strings*******************************/
TestDataConnString = 			"DRIVER=AAA; SERVER=AAA; UID=AAA; PWD=AAA;				DATABASE=AAA";


Data="\[SELECT 
	a.[ShopOrderId] ,[BuildSerialNumber] ,[DateCode] ,a.[ItemNumber], t4.ItemNumber AS PartNumber
    	,string_agg(a.[LotNumber],', ') AS Lots
	FROM [Employee].[dbo].[vw_LotQuery] a
	JOIN Employee.dbo.MFGTravelerShopOrders t2 with (NOLOCK) ON t2.ShopOrderID=a.ShopOrderId
    JOIN Employee.dbo.MFG_ItemMasters t4 with (NOLOCK) ON t4.ItemNumber=t2.ItemNumber
 
	WHERE 
		col IN ¤sql_in¤

		AND
		  (a.[ItemNumber] LIKE '%PM%' OR a.[ItemNumber] LIKE '%PW%')
	Group by a.[ShopOrderId] ,[BuildSerialNumber] ,[DateCode] ,a.[ItemNumber], t4.ItemNumber
	Order by [BuildSerialNumber]

	]\";
	
sql_str = Eval Insert(Data, "¤");
show(sql_str);	

[#7 · Accepted solution]

After you have the query, you have to run the query. I would suggest using New SQL Query() instead of Open Database() as it is the latest method and only method being developed further (even though it is the most user unfriendly). 

[#8 · Accepted solution]

Hi @ConfidenceOwl94 
See "Import Matching Data from an Existing Data Table" in the help documentation under Build a SQL query.

From the red triangle in the Filters column, select Filter Type, and then select Match Column Values.

cheers,

Stan