Help consolidating jmp queries and scripts.

[#1]

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.

1. 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?
2. Although I am opening the tables I dont want to be visible as Private, they keep showing up nevertheless. What am I doing wrong?
3. 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?

[#2]

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

[#3]

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;");

[#4]

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

[#5]

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:

1. In JMP, click on Preferences > Tables.  Make sure ODBC Hide Connection String is unchecked.
2. Click on File > Database > Open Table
3. Click on New Connection
4. If you don't see an Oracle ODBC driver listed under Machine Data Source click New
5. Select the appropriate Oracle driver.  On my machine it is "Oracle in OraClient 12Home1"
6. Click Finish
7. You'll come to an Oracle ODBC Driver Configuration screen
8. Select the appropriate TNS service name and user ID.
9. Click Test Connection to try the connection out
10. Click OK, OK
11. The Database Open Table screen will show a list of schemas
12. Click on a schema and tables will appear.
13. Click on a table and then click on Open Table
14. 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.

[#6 · Accepted solution]

@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 );

[#7]

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.

[#8]

@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() /*###*/;

[#9]

@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.

[#10 · Accepted solution]

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.