topic Re: Create string of unique column value for SQL data pull in Discussions

Create string of unique column value for SQL data pull

ConfidenceOwl94 — Fri, 06 Jun 2025 16:52:15 GMT

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.

Re: Create string of unique column value for SQL data pull

jthi — Fri, 06 Jun 2025 17:08:50 GMT

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')"

Re: Create string of unique column value for SQL data pull

stan_koprowski — Fri, 06 Jun 2025 17:17:53 GMT

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

Re: Create string of unique column value for SQL data pull

ConfidenceOwl94 — Fri, 06 Jun 2025 17:29:10 GMT

Thanks Jarmo,

What am I doing wrong?

Re: Create string of unique column value for SQL data pull

jthi — Fri, 06 Jun 2025 17:55:50 GMT

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.

Re: Create string of unique column value for SQL data pull

ConfidenceOwl94 — Mon, 09 Jun 2025 15:28:49 GMT

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

Re: Create string of unique column value for SQL data pull

ConfidenceOwl94 — Mon, 09 Jun 2025 15:31:45 GMT

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);

Re: Create string of unique column value for SQL data pull

jthi — Mon, 09 Jun 2025 16:01:00 GMT

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).