topic Re: Select/Where Statement Pulls from List in Discussions

Select/Where Statement Pulls from List

SampleIguana839 — Thu, 08 Jun 2023 16:29:00 GMT

Hi!

Not sure if the title describes this issue the best, but I feel like I am on the verge of getting this figured out.

I have one JMP table with a column TestID and I want to select rows from another data table based on the variable TestID list.

dt = Open("Lot List2.jmp");

ID_list = Column( dt, "TestID" ) << Get values;

dt1= Open Database("Connection",
"SELECT
[TestID]
,[Device]
,[FailureMode]
FROM [Database]

where TestID IN 'ID_list'

order by [TestID] Desc","Device Summary");

/* ALSO TRIED*/


dt = Open("Lot List2.jmp");

ID_list = Column( dt, "TestID" ) << Get values;

dt1= Open Database("Connection",
"SELECT
[TestID]
,[Device]
,[FailureMode]
FROM [Database] m

where m.TestID like 'ID_list'

order by [TestID] Desc","Device Summary");

My issue lies somewhere in my 'where' statement, but I am not sure how to call the string of IDs into this statement. The IN statement option does not open a new table, and the like option opens a blank table. My guess is that it treats ID_list as the variable to look for in the other table.

I found a similar question, but the IN statement is not working for me either: https://community.jmp.com/t5/Discussions/how-to-extract-data-with-criteria-of-JMP-table-values/td-p/9434

Any insights are appreciated!

Re: Select/Where Statement Pulls from List

jthi — Wed, 15 Mar 2023 06:55:09 GMT

ID_list will return you a list of strings (or numbers) and you will have to convert it into a string that SQL query will understand and then add that string to your query. Below is one example of query building using Concat Items and Eval Insert

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

ID_list = Column(dt, "name") << Get values; // list of strings?

sql_template = "SELECT [TestID], [Device], [FailureMode]
FROM [Database]
where TestID IN ('¤idlist_sql¤')
order by [TestID] Desc";

idlist_sql = Concat Items(ID_list, "','");

sql_str = Eval Insert(sql_template, "¤");
	
dt1 = Open Database("Connection",
	sql_str
	"Device Summary"
);

This presentation might give some additional ideas

Re: Select/Where Statement Pulls from List

SampleIguana839 — Wed, 15 Mar 2023 13:49:09 GMT

Oh okay I see the modifications you have to do. I have used this template, but I am still note getting another data table to appear. what is this symbol, ¤ ?

Re: Select/Where Statement Pulls from List

pmroz — Wed, 15 Mar 2023 14:28:28 GMT

The default character for eval insert is "^". I didn't know you could supply another one. Looks like Jarmo is using ¤ for that.

To debug:

Use print or show to display your SQL statement.
Copy/paste it into a program that can execute directly on your database. For example for Oracle we use PL/SQL Developer, TOAD or SQL Developer

Once you have that working, run it from JMP.

Here's some sample code:

// List of numeric IDs
id_list = {111, 222, 333, 444, 555};

// Convert numbers to text
for (i = 1, i <= nitems(id_list), i++,
	id_list[i] = char(id_list[i]);
);

in_list = "('" || concat items(id_list, "', '") || "')";
show(in_list);

sql_statement = evalinsert(
"SELECT * from mytable m
  WHERE m.id IN 
^in_list^"
);

show(sql_statement);

Try running this and look for the results in the log window.