topic Re: Help consolidating jmp queries and scripts. in Discussions

Help consolidating jmp queries and scripts.

spoikayil — Fri, 09 Jun 2023 23:27:29 GMT

I started using jmp recently for importing manufacturing data directly to jmp tables and analysing them.

Typically my requirement is to extract the data for each process step in the manufacturing process, for all the batches processed and making charts from them.

So I started with making jmpqueires for each of the processes - and often I want to give the process code as well as the date from which i want the extraction as inputs to the query.

All my jmpqueries therefore has following filters , and I have kept the date as a prompt variable so that I can change it every time I run the query

I also have some post processing script as almost always the data is not stored in the format and shape as I want it to plot charts. This forces me to create a new table from the table that comes out with the original query's extraction.

So, all my jmpqueries end up generating two tables an example is shown below;

This was working pretty good, until now, I want to start consolidating these queries by joining different tables generated by my many different jmp queries.

So I start writing the below script which goes:

finCDquery = open( "PATH\New M33 final CD.jmpquery", Private );
devCDquery = open( "PATH\New M33 develop CD.jmpquery", Private );

M33finCD = finCDquery << Run Foreground(Private);
M33devCD = devCDquery << Run Foreground(Private);

Data Table( "M33-CD QUERY" ) << Join(
	With( Data Table( "M33D-CD QUERY" ) ),
	Select(.
.
.
.

Now, here are the things I would like to have some help with; because I am not quite getting the expected response.

First, every query has the date prompt so I need to wait and give the date and click ok several times in between - how can I modify the queries such that the first date i give is taken and used for all subsequent jmp queries?
Although I am opening the tables I dont want to be visible as Private, they keep showing up nevertheless. What am I doing wrong?
I have the SQL queries for all my jmpqueies, how can I put them in the script rather than opening each of my jmpquery files as I am doing now?

Re: Help consolidating jmp queries and scripts.

pmroz — Fri, 29 May 2020 12:31:51 GMT

If you have the SQL queries, you could switch over to using execute SQL. Here's how to execute two queries using the same date range. Note that the TO_DATE function is Oracle syntax.

dbc = create database connection("<connection string>");

start_date = "01-Jan-2020";
end_date   = "30-Apr-2020";
sql1 = evalinsert(
"SELECT m.foo, m.bar
   FROM myschema.mytable m
  WHERE m.some_date BETWEEN TO_DATE('^start_date^', 'DD-MON-YYYY')
                        AND TO_DATE('^end_date^', 'DD-MON-YYYY')");

dt1 = execute sql(dbc, sql1);

sql2 = evalinsert(
"SELECT a.field1, a.field2 
   FROM myschema.another_table a 
  WHERE a.some_date BETWEEN TO_DATE('^start_date^', 'DD-MON-YYYY')
                        AND TO_DATE('^end_date^', 'DD-MON-YYYY')");

dt2 = execute sql(dbc, sql2);

close database connection(dbc);

Re: Help consolidating jmp queries and scripts.

spoikayil — Fri, 29 May 2020 14:12:02 GMT

Thank you @pmroz for your feedback, I tried this. I copied the connection string in my jmpquery, but the script doesnt run, and the log gives the following error:

[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified\

However the same connection works fine when I run the jmpqueries. What might be off here?

My connection string:

MYDB = create database connection("ODBC:DSN=*****;UID=*****;PWD=*****;DBQ=*****;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;STE=F;TSZ=8192;AST=FLOAT;");

Re: Help consolidating jmp queries and scripts.

spoikayil — Fri, 29 May 2020 14:20:33 GMT

P.S: I am using 32 bit JMP14.1.0 and my Oracle driver is also 32 bit

Re: Help consolidating jmp queries and scripts.

pmroz — Fri, 29 May 2020 15:56:53 GMT

I've had trouble with the Microsoft Oracle ODBC driver. The one from Oracle works fine though.

It's a bit tedious, but here's how I got the connection string:

In JMP, click on Preferences > Tables. Make sure ODBC Hide Connection String is unchecked.
Click on File > Database > Open Table
Click on New Connection
If you don't see an Oracle ODBC driver listed under Machine Data Source click New
Select the appropriate Oracle driver. On my machine it is "Oracle in OraClient 12Home1"
Click Finish
You'll come to an Oracle ODBC Driver Configuration screen
Select the appropriate TNS service name and user ID.
Click Test Connection to try the connection out
Click OK, OK
The Database Open Table screen will show a list of schemas
Click on a schema and tables will appear.
Click on a table and then click on Open Table
The table will have a program called Source. Right click on the green triangle and select Edit - that will show you the correct connection string.

Like I said tedious but once you have the connection string you're good to go. Don't forget to check the ODBC Hide checkbox in preferences.

Re: Help consolidating jmp queries and scripts.

Georg — Fri, 29 May 2020 22:21:50 GMT

@spoikayil , Please note that there are several Methods for querying data from databases, needing somehow different Connections.

For the .jmpquery the Connection String Needs to start with "ODBC: …", and for the others you Need to delete it, see jsl example. Otherwise you get exactly the error you posted. It took me some time to figure it out in the past.

The example generates 3 tables using different Methods.

// Three methods to get data from database via ODBC

Names Default To Here( 1 );

// provide your credentials here (DSN, USER, PWD, DBQ)
cnx_str1 = "ODBC:DSN=__MY_DSN__;UID=__USER__;PWD=__PWD__;DBQ=__MY_DBQ__;";
cnx_str2 = "DSN=__MY_DSN__;UID=__USER__;PWD=__PWD__;DBQ=__MY_DBQ__;";

sql_str = "select * from dual";

//**** 1st method
dt1 = New SQL Query( connection( cnx_str1 ), queryname( "dt_jmpquery" ), customSQL( sql_str ) ) << Run Foreground();

//**** 2nd method
// This fails due to cnx starting with "ODBC:"
// dt2 = open database(cnx_str1, "select * from dual");
// This is ok
dt2 = Open Database( cnx_str2, sql_str, "dt_open_database" );

//**** 3rd method
cnx = Create Database Connection( cnx_str2 );
dt2 = Execute SQL( cnx, sql_str, "dt_execute_SQL" );
Close Database Connection( cnx );

Re: Help consolidating jmp queries and scripts.

pmroz — Sun, 31 May 2020 00:18:50 GMT

To add to what @Georg said, my Oracle connection string looks like this:

dsn_string = "Driver={Oracle in OraClient12Home1};Dbq=tns_alias_goes_here;UID=username_goes_here;PWD=password_goes_here;";

Note that the part that says "Oracle in OraClient12Home1" is the name of the Oracle ODBC driver.

Re: Help consolidating jmp queries and scripts.

spoikayil — Mon, 01 Jun 2020 19:03:10 GMT

@pmroz , @Georg thanks very much for your feedback!

I verified the preferences were as suggested by pmroz, and then tried method 1, that Georg had recommended - assignig the connection string and SQL query to separate variables and runnig the query with the New SQL Query() method.

It seems to work - as I no longer get the ODBC driver error I used to get before.

However, I get a new error like below; and I am not sure if that is due to a bad query or not.

I have copied the query from the jmpquery file's SQL tab. I changed the double quotes for the column alias 'M32-FICD-DATE' for the REVDATE column to single quotes, as otherwise it catches the wrong double quote as the end of the string.

[Oracle][ODBC][Ora]ORA-00923: FROM keyword not found where expected [SQLSTATE=HY000] in access or evaluation of 'Run Foreground' , Run Foreground() /*###*/

In the following script, error marked by /*###*/
Names Default To Here( 1 );
cnxstrng =
"ODBC:DSN=****;UID=*******;PWD=******;DBQ=*****;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=NLS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;MLD=0;ODA=F;STE=F;TSZ=8192;AST=FLOAT;";
M32FICDSQL =
"SELECT t1.LOTID, t2.COMPIDS AS WAFERID, t1.EQPID, t2.REVDATE AS 'M32-FICD-DATE', 
	t1.PARTID, t1.RECPID, t2.ITEMPROMPT, t2.NUMERICVALUE, 
	t3.LOTTYPE 
FROM PLLDBA.TRES  t1 
	LEFT OUTER JOIN PLLDBA.ACTL t3 
		ON  ( t1.LOTID = t3.LOTID )  
	INNER JOIN PLLDBA.TRES_RAWDATA t2 
		ON  (  ( t1.TESTOPNO = t2.TESTOPNO )  AND  ( t1.REVDATE = t2.REVDATE )  )  
WHERE  (  (  (  (t1.TESTOPNO LIKE  '%JG43%' )  )  AND  ( t2.REVDATE >= TO_TIMESTAMP('01Jan2019:18:42:42', 'DDMONYYYY:HH24:MI:SS') )  AND  ( t2.ITEMPROMPT IN  ( 'ITEM1' ,  'MITEM2' ,  'ITEM3' ,  'ITEM4' )  )  AND  (  (t1.RECPID LIKE  '%HJ546%' )  )  AND  (  (t3.LOTTYPE LIKE  '%PT%' )  )  AND  (  (t1.PARTID LIKE  '%C765G%' )  )  )  ) 
ORDER BY t2.COMPIDS DESC, t2.REVDATE ASC;";
dt1 = New SQL Query(
	connection( cnxstrng ),
	queryname( "M32FICD" ),
	customSQL( M32FICDSQL )
) << Run Foreground() /*###*/;

Re: Help consolidating jmp queries and scripts.

Georg — Mon, 01 Jun 2020 18:58:50 GMT

@spoikayil , you´re on the Right way, the Connection seems to be ok.

Your error is an SQL error, the ODBC Driver tells you that there is an error before your "from" Statement in the SQL. So I think, there is a Problem with "-" in your column Name 'M32-FICD-DATE', even when quoted. You get the same error, when missing a colon (,) in the select Statement. Never use "-" in Names, and never use double quotes (as you mentioned already).

Writing/developing SQL Queries in JMP (Scripting error) is very difficult, because you easily make Syntax Errors like here. And there are a lot more issues with SQL in scripts, some of them you even can't see (some hidden characters …). Although I'm an experienced SQL writer meanwhile, I Always use a different tool like SQL developer .. for writing SQL. With JMP it is not possible to write plain SQL in my opinion, at least for complex queries. But of Course you can use the graphical query builder for simple queries.

Slightly better it looks, when you Substitute "Run Foreground()" by "Modify()", then you get the query Editor opened, and you can better Read your query, and also test it.

Re: Help consolidating jmp queries and scripts.

pmroz — Mon, 01 Jun 2020 19:03:46 GMT

The error is because you're trying to set the column header using single quotes. Oracle wants the column name to be enclosed in double quotes. This should work - note that I'm using \[ and ]\ to allow double quotes to be in the string without those pesky escape characters.

M32FICDSQL =
"\[SELECT t1.LOTID, t2.COMPIDS AS WAFERID, t1.EQPID, t2.REVDATE AS "M32-FICD-DATE", 
	t1.PARTID, t1.RECPID, t2.ITEMPROMPT, t2.NUMERICVALUE, 
	t3.LOTTYPE 
FROM PLLDBA.TRES  t1 
	LEFT OUTER JOIN PLLDBA.ACTL t3 
		ON  ( t1.LOTID = t3.LOTID )  
	INNER JOIN PLLDBA.TRES_RAWDATA t2 
		ON  (  ( t1.TESTOPNO = t2.TESTOPNO )  AND  ( t1.REVDATE = t2.REVDATE )  )  
WHERE  (  (  (  (t1.TESTOPNO LIKE  '%JG43%' )  )  AND  ( t2.REVDATE >= TO_TIMESTAMP('01Jan2019:18:42:42', 'DDMONYYYY:HH24:MI:SS') )  AND  ( t2.ITEMPROMPT IN  ( 'ITEM1' ,  'MITEM2' ,  'ITEM3' ,  'ITEM4' )  )  AND  (  (t1.RECPID LIKE  '%HJ546%' )  )  AND  (  (t3.LOTTYPE LIKE  '%PT%' )  )  AND  (  (t1.PARTID LIKE  '%C765G%' )  )  )  ) 
ORDER BY t2.COMPIDS DESC, t2.REVDATE ASC;]\";

If you keep having trouble don't use the AS command and instead change the column name in JSL. You shouldn't have to - I use this syntax all the time with no problems.

Re: Help consolidating jmp queries and scripts.

Georg — Mon, 01 Jun 2020 19:35:02 GMT

@pmroz thanks for your Suggestion using general Quotation \[ \], this is another way I even didn't think of.

Unless it might work that way, I personally don't like Hyphens in names and double quotes in SQL …, because I had a lot of pain due to that … in past.

Re: Help consolidating jmp queries and scripts.

pmroz — Tue, 02 Jun 2020 11:52:30 GMT

It's difficult to develop SQL in JSL - as @Georg mentions use a tool like SQL Developer or PL/SQL Developer.

I developed the log_execute_sql function a few years ago - it's posted in the file exchange:

That's become an invaluable aid for us to debug SQL in JSL.

Re: Help consolidating jmp queries and scripts.

spoikayil — Mon, 01 Jun 2020 19:45:12 GMT

@pmroz @Georg Thanks guys!!
Enclosing the query in \[ does the trick for the second issue.