878711_en-US

liliconcom

Create string of unique column value for SQL data pull

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.

 

Basic Data Analysis and ModelingRe: Create string of unique column value for SQL data pull

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

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

Thanks Jarmo,

 

What am I doing wrong?

 

 

 

 

 

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

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

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

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

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