14982_en-US

liliconcom

SQLFILE and Variables<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Hi guys,

I am trying to tidy my script up. I have embedded in my script a very long sql query which on the top level is:

sqlQuery = "Select x from table where identifier =" || primaryidendtifer ||";"

I have saved this in an external .sql file and thought that I could load the query into the script to evaluated when required.

JMP doesnt recognise that it needs to place the variables into the sql script

Any help please

Re: SQLFILE and Variables<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />

Something like

primaryidendtifer = 0;

sqltext = load text file("path.sql");

sqlQuery = substitute(sqltext, "primaryidendtifer", primaryidendtifer);

Alternatively,

Save ' sqlQuery = "Select x from table where identifier =" || primaryidendtifer ||";" ' into a .jsl file. Then use the include statement.  Including another JSL file will pass variables between the two scripts.  Just be careful not to overwrite common variables like "i" in for loops.

include("path.jsl");

Re: SQLFILE and Variables

Here's another approach that uses save text file, load text file, and evalinsert.  With evalinsert you surround your variables with the caret character (^), and it substitutes the currently assigned variable values in to your string.

// Save the query to a file with a placeholder for the identifier

sqlQuery = "Select x from table where identifier = ^primaryidentifier^";

save text file("c:\temp\myquery.sql", sqlquery);

// Load the contents of the text file into a variable

sqlQuery = load text file("c:\temp\myquery.sql");

// Set the identifier using evalinsert

primaryidentifier = "12345";

sql_statement = evalinsert(sqlquery);

print(sql_statement);

"Select x from table where identifier = 12345"