50692_en-US

liliconcom

JMP 13 JSL that passes variables to oracle SQL statement

I am working with a collegue that has a JMP 13 script to is defining variables using Modal windows.  User selection then sets variables that we want to pass into a open database section of our script in the SQL statement.  Data table is updated for which oven is selected in the Modal window.  We then select the oven column to pass this value into our SQL statement.

 

Example.

 

OvenList={18,22};
nw=New Window( " Reference Units",<<MODAL></MODAL>

 

 

Issue we have is the following...

     oven_id: script passes [2,2]

     test_part_id: script passes {"partid1","partid2"}

 

Questions

1) how do we select the first value in the oven column to set as the oven_id that gets passed to SQL statement?

2) Oracle appears to not like the parenthese and double quotes. It works if we hard code single quotes and part ids  ex: IN('partid1','partid2')

 

HELP!!!

Re: JMP 13 JSL that passes variables to oracle SQL statement

Glad it worked for you.  The ^varname^ construct needs to be used with evalinsert.  The use of evalinsert allows somewhat cleaner looking code, and you don't need to convert numbers to strings with char().

test_part_in_list = "('" || concat items(test_part_id, "', '") || "')";
sql = evalinsert(
"Select db.table.field1
        db.table.field2
        db.table.field3
   FROM db.table
  WHERE db.table.exprmt = 'blah'
    AND db.table.field = ^oven_id[1]^
    AND db.table.field In ^test_part_in_list^ ORDER BY test_part_id ASC");

Open Database( -- connection string info -- , sql, "dataset");

Re: JMP 13 JSL that passes variables to oracle SQL statement

pmroz,

Thanks for your quick reply and code suggestion.  It worked perfect for our application.  Thank you very much.  We tried all kinds of solutions from various forums using ^var_name^, ^||var_name||^, etc.  Your solution by building the list was the solution step we were missing.

 

 

Re: JMP 13 JSL that passes variables to oracle SQL statement

Get values returns a list, so you need to refer to individual array elements in order to use them.  You can use concat items to create an IN list.  This will work:

test_part_in_list = "('" || concat items(test_part_id, "', '") || "')";
sql = 
"Select db.table.field1
        db.table.field2
        db.table.field3
   FROM db.table
  WHERE db.table.exprmt = 'blah'
    AND db.table.field = " || char(oven_id[1]) || "
    AND db.table.field In " || test_part_in_list || "ORDER BY test_part_id ASC";

Open Database( -- connection string info -- , sql, "dataset");