topic script into sql in Discussions

script into sql

Isabel26 — Fri, 09 Jun 2023 17:02:51 GMT

Hello All,

I always need to turn a list into a format that can run as sql, like this:

LIST

into in('A','B','C','D','E')

Right now, I have to use 'query builder->filter the table->select from a list' then copy the [ in('A','B','C','D','E') ] from there. Is there a scripting way that I can use as template no matter what list I work on? Like I run the same code then get the list in sql way. Very trouble to click through all the way to get it every day. Thanks so much!

Re: script into sql

txnelson — Wed, 29 Jun 2022 13:58:34 GMT

The Concat Items() function should give you what you want

theList = {"A","B","C","D","E"};
inString = concat items(theList,",");

Re: script into sql

Isabel26 — Wed, 29 Jun 2022 14:14:40 GMT

Thanks and this will put list in the format, but my list normally has thousands entries, and that is impossible to put in theList ={};

Re: script into sql

txnelson — Wed, 29 Jun 2022 16:11:03 GMT

In my first response, I was actually just pointing you to the Concat Items() function, so you would have it to possibly work into your solution. You did not provide what the structure of the "LIST" input actually was. So guessing again, here is the same function being used but with the "LIST" not being in the form of a JMP List, but instead coming from a data table column called "name".

Here is a slight modification that takes the list from a data column. The column can be as long as you want

Names Default To Here( 1 );
// Open Data Table: big class.jmp
// → Data Table( "big class" )
dt = Open( "$SAMPLE_DATA/big class.jmp" );

inString = "'" || Concat Items( dt:name << get values, "','" ) || "'";

Re: script into sql

Isabel26 — Thu, 30 Jun 2022 17:15:38 GMT

This works fine and thanks for the reply. Yes the NAME column is what I mean. I do find out that it stopped instring after about 400 rows. So, it will work for data less than 400 rows, but still a problem for larger data.

Re: script into sql

txnelson — Thu, 30 Jun 2022 20:00:08 GMT

If you have that many comparisons to make, most of the time, I upload a table through SQL to the database. I then join the comparison table with the table of interest, and then I