214969_en-US

liliconcom

Extract data from a ODBC into a table using JSL

Hi 

New to JSL...

Trying to create a JMP data table that contain data from an ODBC. I need only values after a certain date and only certain products. 

My current script for one product looks like this: 

SELECT * FROM "LIMS"."SAMPLE" WHERE LIMS.SAMPLE.DATE >= to_date('17-01-2015', 'dd-mm-yyyy') AND LIMS.SAMPLE.PRODUCT = '12345ABC'

How do I include more than one product e.g. three products 12345ABC, 23456ACD, 1234ABCD to my table with such a script?

Best,

M

 

Re: Extract data from a ODBC into a table using JSL

Hi MBL,

 

I would encourage you to use the Query Builder in JMP.  Once you have the results that you like for the Query, generate the table and then just copy/paste the source script out of the result and put that into your JSL. This will make the whole process much easier.

 

If you want to proceed with the SQL below, I believe you would do something like:

SELECT * FROM "LIMS"."SAMPLE" WHERE LIMS.SAMPLE.DATE >= to_date('17-01-2015', 'dd-mm-yyyy') AND (LIMS.SAMPLE.PRODUCT IN ( '12345ABC', '23456ACD', '1234ABCD'))

 

Brian Corcoran

JMP Development

 

Re: Extract data from a ODBC into a table using JSL

Here's one way to generate your SQL.  The trick is to use the Concat Items function to create an IN list.  I'm also using evalinsert and \[...]\ so that I can include double quotes inside the string cleanly.

prod_list = {"12345ABC", "23456ACD", "1234ABCD"};

in_list = "('" || Concat Items(prod_list, "','") || "')";

sql = evalinsert(
"\[SELECT * FROM "LIMS"."SAMPLE" 
    WHERE LIMS.SAMPLE.DATE >= to_date('17-01-2015', 'dd-mm-yyyy') 
      AND (LIMS.SAMPLE.PRODUCT IN ^in_list^)]\");

show(in_list, sql);

Results in the log window:

in_list = "('12345ABC','23456ACD','1234ABCD')";
sql = "SELECT * FROM \!"LIMS\!".\!"SAMPLE\!" 
    WHERE LIMS.SAMPLE.DATE >= to_date('17-01-2015', 'dd-mm-yyyy') 
      AND (LIMS.SAMPLE.PRODUCT IN ('12345ABC','23456ACD','1234ABCD'))";