topic Re: JMP and Dynamic SQL queries in Discussions

JMP and Dynamic SQL queries

cookiedough — Tue, 05 Feb 2019 21:48:36 GMT

Does anyone know if JMP supports some form of Dynamic SQL queries? I want to do some special querying that is easier to dynamically generate the sql query than directly writing it.

Re: JMP and Dynamic SQL queries

gzmorgan0 — Thu, 07 Feb 2019 02:16:05 GMT

You did not mention which version of JMP that you are using. JMP has an interactive Query Builder. From Help on the JMP Main Menu, select Books > Using JMP > Chapter 3 Import Data. There is an entire section on connecting to the data base and selecting DB tables and joining and selecting and creating functions/aggregates, etc.

The user interface also allow you to see the SQL statements generated by your point and click selections.

Often the same type of data data needs to be created, but different data ranges, or a different set of variables are needed. If you search the blog, you should find several examples of scripts people have written to prompt a user for a few criteria that is used to modify the base SQL, and run the query.

BTW, Query Builder also allows you to add a filter prompt, so some of the scripting is built-in. Uness you provide more details by what you mean by dynamic, that is all the info I have to offer.

Re: JMP and Dynamic SQL queries

pmroz — Wed, 06 Feb 2019 12:50:10 GMT

Yes JMP supports dynamic SQL queries. You can use something like the following:

product = "ABCDEFG";
sql_statement = evalinsert(
"SELECT * 
   FROM mytable m
  WHERE m.product = '^product^'");
dbc = create database connection("Database Connection String goes here");
dt = execute sql(dbc, sql_statement);
close database connection(dbc);