BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
Highlighted
cookiedough
Community Member

JMP and Dynamic SQL queries

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. 

0 Kudos
2 REPLIES 2
gzmorgan0
Super User

Re: JMP and Dynamic SQL queries

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.

pmroz
Super User

Re: JMP and Dynamic SQL queries

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);
0 Kudos