Pull Data using SQL and JMP string in JSL script

[#1]

Hi all.

I am accessing a certain database using SQL from a JSL script.

To pull the data I want, I need to provide a specification.

The specification comes in the form of a list or matrix in JMP/JSL.

If I manually type-in the single items names, it works. =>

/* Fetch Single Items from Database*/
	A = Open Database("Server Specs", "SELECT * FROM CertainFolder a WHERE a.CertainColumn IN ('A','B','C','D')");

But, since the items might be thousands, I need to find a way to use a single name representing a list (or a matrix) of the items.

Of course, like this is not working. =>

/* Fetch Data from Data Table*/
	Items_M = :Items << get as matrix();

/* Fetch Data from Database*/
	A = Open Database("Server Specs", "SELECT * FROM CertainFolder a WHERE a.CertainColumn IN ('^Items_M^')");

What is the simplest way to do this?

Thank you!

[#2 · Accepted solution]

I can't say, what is simplest, but this is how I do it:

// for test purposes 
lstItems = {"A","B","C","D"};

// build a comma separated list
strItems = ConcatItems(lstItems,",");

/* Fetch Data from Database*/

eval(parse(evalinsert("\[ 

	A = Open Database("Server Specs", "SELECT * FROM CertainFolder a WHERE a.CertainColumn IN (^strItems^)")
	
]\")));;

[#3]

Thank you very much!

That it what I was looking for... It wouldn't work during the weekend because I had unstable access to the database.

Thanks again.

[#4 · Accepted solution]

This is how I usually do it:

• building a unique list of column values
• feeding that list into an sql
• get data back from database into a new table

It is so far a I know the simplest way to perform this task, and I use it a lot,

I have implemented this in my menu, to have it at hand quickly.

// take unique values from the column :items
dt = open("$SAMPLE_DATA\Big Class.jmp");
summarize( Current Data Table(), item_lst = by( :NAME /* :item */ ) );
// show list
show(item_lst);
// prepare list for sql
item_lst_str=substitute (char(item_lst),"{","(","}",")","\!"","'");

// open SQL Query with inserted item_lst
New SQL Query(
	Connection(
		"ODBC:DSN=DSN_NAME;UID=user;DBQ=database;" // connection string to be modified
	),
	QueryName( "ITEMS_Query" ),
	CustomSQL( eval insert("SELECT * from database_table dt where dt.item in ^item_lst_str^;") )
) << modify;
// or directly run if neccessary
// ) << Run;

[#5]

Thanks!

This works as well. And it is very useful!

Best,

Valerio

[#6]

I changed by example to use Concat Items function which does the job of converting a list into a comma separated string. Your code used ^^ which is the smart way of doing an insert, but it only works in conjunction with the Eval Insert function.