topic Re: JMP and SQL queries in Discussions

JMP and SQL queries

Stas — Sat, 10 Jun 2023 23:27:35 GMT

Hey guys, I have two related questions that would be great if I'll find a solution to:
1. is there a way that JMP can alter a field in SQL query? I mean that I wish to run JSL code that will ask from a user the date range (for example), it will update it into the relevant field in my SQL query and will run this query.

2. Is it possible to create JSL code that will ask from the user if to run X query or Y query and based on the answer it will run the chosen query?

For both questions, right now my code runs queries that been converted to BAT files (used to work with EXE files but SQL no longer supports it as standard.
Run button example:
Button Box("xx",

open("\\VMSPFSFSLC01\F28_PLI\SQL\FE SQL\xx.bat")
),

Re: JMP and SQL queries

Georg — Sun, 21 Mar 2021 08:28:53 GMT

May be you want to have a look at a recent paper:

Strategies and Examples for Data Acquisition From Distributed and Complex Source... - JMP User Community

In the video you find, how it works, and in the journal you find all scripts working with Big Class.jmp sample table.

But pretty much the same works also on ODBC connections for databases.

Re: JMP and SQL queries

Mauro_Gerber — Mon, 22 Mar 2021 07:16:40 GMT

Hello Stas

You can concat your SQL code snippets as you like. I have two samples to give you an idea how to solve your problem:

OrderNumber = 12345;

text_block_DB_0 = "select  t1.*  from DB1 t1";

if(selection_button, // can be a choice of the user
	text_block_DB_1 = " and t1.[time]=(select max(t2.[time]) from DB1 t2 where t1.[SN] = t2.[SN])"; // get the laste entry of a serial number dependend of time)
	,
	text_block_DB_1 = ""; // gives all SN
)

dt = Open Database(__DSN_String,text_block_DB_0 || " where t1.[Order] = " || char(OrderNumber) || text_block_DB_1);


OrderNumber = 12345;

text_block_DB_0 = "select  t1.*  from DB1 t1 where t1.[Order] = *insert_order_number* ";

if(selection_button,
	text_block_DB_1 = " and t1.[time]=(select max(t2.[time]) from DB1 t2 where t1.[SN] = t2.[SN])";
	,
	text_block_DB_1 = "";
)

dt = Open Database(__DSN_String,substitute(text_block_DB_0,"*insert_order_number*", char(OrderNumber)) || text_block_DB_1);