nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Create string of unique column value for SQL data pull

ConfidenceOwl94

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.

Accepted answers

stan_koprowski

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

jthi

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

All comments

jthi

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

stan_koprowski

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

ConfidenceOwl94

Thanks Jarmo,

What am I doing wrong?

jthi

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.

ConfidenceOwl94

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