topic Calendar Box() - Date Range - Convert Date/Time - SQL[Database] in Discussions

Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

Yngeinstn — Sun, 09 Dec 2018 15:11:15 GMT

Greetings.

My company finally was able to get JMP14 realeased so I am very excited to try out the new features. Specifically the Semiconductor Took Kit and the Calendar Box() function. So my first step is to parse the large amounts of data pulling from a data range. I finally manged to convert the 3388594698 to an actual date. Here is an example of the output:

sqlstart = "01/18/2018 12:00:00 AM";
sqlend = "12/12/2018 1:25:05 PM";

However our database has the timestamp formatted like 2018-10-28 16:59:03. I have tried to solve this using the Scripting Guide and posts on this website but to no avail. End of script closes the window and will open another one as Part #2 but would like the dashboard to hold everything.. But that is the next hurdle.

Any help would be greatly appreciated.

Close All( Data Tables, No Save );
Close All( Reports );
Clear Log();
Close All( journals, no save );
Clear Symbols();

/******************************************************************************/
/*                                                                            */
/* Brings in the list of wafers from the SQL Query [Current] 	              */
/*                                                                            */
/******************************************************************************/

testdata_HAY_QQ_LotList = Expr(
	"
	SELECT DISTINCT 
		t1.wafer_number,
		t1.testdate
	FROM tst_master  t1
		LEFT OUTER JOIN tst_dataset_header t2 
			ON  ( t2.run_id = t1.run_id )
	WHERE t1.testdate >= cast('2018-01-01' AS date) // Currently How I select a list of lots.
		AND (t1.wafer_number NOT RLIKE '-ATL' )
		ORDER BY t1.testdate DESC
	"
);

/*******************************************************************************/
/*                                                                             */
/* Brings in the list of wafers from the SQL Query [Desired with Caledar Box]  */
/*                                                                             */
/*******************************************************************************/


testdata_HAY_QQ_LotList = Expr(
	"
		SELECT DISTINCT 
		t1.wafer_number,
		t1.testdate
	FROM tst_master  t1
		LEFT OUTER JOIN tst_dataset_header t2 
			ON  ( t2.run_id = t1.run_id )
	WHERE (t1.testdate <= "||sqlstart||") // Varible from Date Selection
		AND (t1.testdate >= "||sqlend||") // Varible from Date Selection
		AND (t1.wafer_number NOT RLIKE '-ATL' )
		ORDER BY t1.testdate DESC
	"
);

/******************************************************************************/
/*                                                                            */
/* LotList is used in the next step of the data pull selecting the wafers     */
/*                                                                            */
/******************************************************************************/


dt_LotList = Open Database( DATABASE, testdata_HAY_QQ_LotList, "LotList" );
Summarize( LotList = By( :wafer_number ) );
Close( dt_lotlist, No Save );


/******************************************************************************/
/*                                                                            */
/* Start of Main Script [Calendar Box() to Select Wafer with Date Range]      */
/*                                                                            */
/******************************************************************************/


nw = New Window( "DATE SELECTION",
	V List Box(
		Align( Left ),
		V List Box(
			Spacer Box(
				Size( 5, 15 )
			),
			Text Box( 
				"LOT DATE RANGE",
				<< Set Font Name( "Segoe UI" ),
				<< Set Font Style( "Bold" ),
				<< Set Font Size( 12 ),
				<< Justify Text( "Center" )
			),
			Spacer Box(
				Size( 5, 20 )
			),
			H List Box(
				Panel Box( "Select Start Date",
					StartDate = Calendar Box(),
				),
				Spacer Box(),
				Panel Box( "Select End Date",
					EndDate = Calendar Box(),
				)
			),
			Panel Box( "",
				loadlot = Button Box( "LOAD LOT LIST",
					<< Set Function(
						Function(
							{self},
							sqlstart = MDYHMS( StartDate << Get Date()); // passed to database query
							sqlend = MDYHMS( EndDate << Get Date());	// passed to database query 
							Show( sqlstart );
							Show( sqlend );
							self << close window;
						)
					)
				)
			)
		)
	)
);

date_start = Date MDY( 01, 1, 2018 );

StartDate << Date( date_start );

StartDate << Show Time( 0 );

StartDate << Min Date( Date Increment( Today(), "Day", -365, "Start" ) );

date_end = Date MDY( 01, 1, 2018 );

EndDate << Date( date_end );

EndDate << Show Time( 0 );

EndDate << Min Date( Date Increment( Today(), "Day", -365, "Start" ) );

Re: Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

david707 — Mon, 10 Dec 2018 08:56:18 GMT

Hi,

My understanding is you want the calander box selection to be used as a date filter in SQL?

If so you'll need 2 script changes:

- Use format to change the date format

- Change your SQL code to include '

I've shown an example below:

/******************************************************************************/
/*                                                                            */
/* Start of Main Script [Calendar Box() to Select Wafer with Date Range]      */
/*                                                                            */
/******************************************************************************/


nw = New Window( "DATE SELECTION",
	V List Box(
		Align( Left ),
		V List Box(
			Spacer Box(
				Size( 5, 15 )
			),
			Text Box( 
				"LOT DATE RANGE",
				<< Set Font Name( "Segoe UI" ),
				<< Set Font Style( "Bold" ),
				<< Set Font Size( 12 ),
				<< Justify Text( "Center" )
			),
			Spacer Box(
				Size( 5, 20 )
			),
			H List Box(
				Panel Box( "Select Start Date",
					StartDate = Calendar Box(),
				),
				Spacer Box(),
				Panel Box( "Select End Date",
					EndDate = Calendar Box(),
				)
			),
			Panel Box( "",
				loadlot = Button Box( "LOAD LOT LIST",
					<< Set Function(
						Function(
							{self},
							sqlstartA = StartDate << Get Date(); // passed to database query
							sqlendA = EndDate << Get Date();	// passed to database query 
							Show( sqlstart );
							Show( sqlend );
							self << close window;
						)
					)
				)
			)
		)
	)
);

sqlstart = format(sqlstartA, "Y/M/D h:m:s");
sqlend = format(sqlendA, "Y/M/D h:m:s");

/*******************************************************************************/
/*                                                                             */
/* Brings in the list of wafers from the SQL Query [Desired with Caledar Box]  */
/*                                                                             */
/*******************************************************************************/


testdata_HAY_QQ_LotList = Expr(
	"
		SELECT DISTINCT 
		t1.wafer_number,
		t1.testdate
	FROM tst_master  t1
		LEFT OUTER JOIN tst_dataset_header t2 
			ON  ( t2.run_id = t1.run_id )
	WHERE (t1.testdate <= '"||sqlstart||"') // Varible from Date Selection
		AND (t1.testdate >= '"||sqlend||"') // Varible from Date Selection
		AND (t1.wafer_number NOT RLIKE '-ATL' )
		ORDER BY t1.testdate DESC
	"
);

Re: Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

Yngeinstn — Wed, 12 Dec 2018 18:48:22 GMT

Thank you for the help.. Can you please elaborate on the "Change your SQL code to include '" please?

Re: Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

pmroz — Wed, 12 Dec 2018 22:02:45 GMT

What database are you using?

You either need to convert your dates to a database date (TO_DATE in Oracle), or pass them as a string, hence the need to wrap the JMP date in single quotes. Is there a way for you to run the SQL code directly against the database, perhaps in Toad or a similar product? That way you can iterate on your code until it works in the database, and then call it in JSL.

BTW rather than using open database I'd recommend create database connection, execute sql, and close database connection. That way you can open a connection to the DB, run one or more queries, and then close the connection. Open Database creates a new connection for every SQL query.

Re: Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

david707 — Thu, 13 Dec 2018 08:30:46 GMT

Hi,

In the SQL where clause any dates need to be surrounded by '

examples:

"
SELECT
*
FROM Runs
WHERE
RunStartDate > '2018-09-01'
";

"
SELECT
*
FROM Runs
WHERE
RunStartDate > ' " || DATE || " '
";

Re: Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

Yngeinstn — Thu, 13 Dec 2018 08:44:56 GMT

I use the '"||something||"' religiously. The problem I have been trying to
solve is when I use '"||sqlstart||"' is an dealing with the concatenation
of that variable which I can only assume is the sqlsart is a numeric value
and I have to convert to character for it to work correctly. When I added
Show() for StartDate, sqlstartA and sqlstart I see the conversation
process. Getting it into the query itself is what has stumped me.

I have been trying to solve this on my own first instead of just coming
here and being told the answer.

Re: Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

david707 — Thu, 13 Dec 2018 09:17:24 GMT

Only mentioned as absent from your example code.

Probably the most useful thing I can do for you now is post a working example that I just ran:

Names Default To Here( 1 );
nw = New Window( "DATE SELECTION",
	<<Modal,
	<<return result,
	V List Box(
		Align( Left ),
		V List Box(
			Spacer Box(
				Size( 5, 15 )
			),
			Text Box( 
				"LOT DATE RANGE",
				<< Set Font Name( "Segoe UI" ),
				<< Set Font Style( "Bold" ),
				<< Set Font Size( 12 ),
				<< Justify Text( "Center" )
			),
			Spacer Box(
				Size( 5, 20 )
			),
			H List Box(
				pb1 = Panel Box(
					"Select Start Date",
					StartDate = Calendar Box(
						<<SetFunction( 
							Function( {f1},
								sqlstartA = StartDate << Get Date();
							)
						),
					),
				),
				Spacer Box(),
				Panel Box(
					"Select End Date",
					EndDate = Calendar Box(
						<<SetFunction( 
							Function( {f2},
								sqlendA = EndDate << Get Date();
							)
						),
					),
				),
			),
			H List Box( Button Box( "OK" ), Button Box( "Cancel" ) ),    
		)
	)
);
wait(0.1);
sqlstart = format(sqlstartA, "Y/M/D h:m:s");
sqlend = format(sqlendA, "Y/M/D h:m:s");
show(sqlstart, sqlend);
wait(0.1);
	ev = Get Environment Variable( "computername" );
	ConnectionString = "DRIVER=SQL NATIVE CLIENT; SERVER=***; DATABASE=***; Uid=***;Pwd=***"
	Try(Close database connection(SQLConnection)); 
	SQLConnection = Create database connection(ConnectionString);
	SQLcode =
		"
	SELECT
		*
	FROM Runs
	WHERE
	runStartTime >= ' " || sqlstart || " ' AND runStartTime <= ' " || sqlend || " '
	";
	dt = Execute SQL(SQLConnection, 
	SQLcode,
		   "dt"
	);

Re: Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

Yngeinstn — Thu, 13 Dec 2018 09:25:01 GMT

Thank you.. I will try this as soon as i get to work.

Re: Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

pmroz — Thu, 13 Dec 2018 13:21:04 GMT

You can use evalinsert for cleaner looking code and automatic conversion of numeric values to character strings. Simply surround your variable with the caret character (^) like this:

testdata_HAY_QQ_LotList = evalinsert(
"SELECT DISTINCT 
		t1.wafer_number,
		t1.testdate
	FROM tst_master t1
		 LEFT OUTER JOIN tst_dataset_header t2 
			ON ( t2.run_id = t1.run_id )
	WHERE (t1.testdate <= '^sqlstart^') /* Varible from Date Selection */
	  AND (t1.testdate >= '^sqlend^') /* Varible from Date Selection */
	  AND (t1.wafer_number NOT RLIKE '-ATL' )
	ORDER BY t1.testdate DESC");

Re: Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

Yngeinstn — Thu, 13 Dec 2018 15:39:27 GMT

Thank you very much for your assistance.. Works like a charm.

Re: Calendar Box() - Date Range - Convert Date/Time - SQL[Database]

Yngeinstn — Fri, 14 Dec 2018 15:02:34 GMT

I have a couple of follow-up questions if I may

- In the code you provded with the connection string, I had an issue with SQL NATIVE CLIENT as I noticed it is defaulting to the 32bit MySQL driver. Our databases use the 64bit driver. I looked online and couldn't find the replacement for the NATIVE CLIENT.

- Does JMP have the ability to join tables virtually and store all the information without bringing them into tables? We have 4 main tests and within those 4 tests we have 8 subtests so for one wafer's worth of data it stands at ~61,000 rows x 24 columns if i ping the server and bring it into a table. In retrospect we have about ~50 wafers worth of data so you can see how large the data set could be. I would like to use the Dashbord function to filter and produce the reports that we need to do our analysis.

As always, I appreciate the help and support from the commuinity. If this is confusing please don't hesitate to reach back out for clarification. I changed quite a few things so not to reveal too much detail about our tests

// 32bit SQL Driver
	
ev = Get Environment Variable( "computername" );
	ConnectionString = "DRIVER=SQL NATIVE CLIENT; SERVER=***;  DATABASE=***; Uid=***;Pwd=***" // Need 64 bit
	Try(Close database connection(SQLConnection)); 
	SQLConnection = Create database connection(ConnectionString);

// Main SQL Query that brings in all data for a wafer(s)
	"
SELECT
	CASE
		WHEN t2.testname = TEST_1 THEN t3.ts	 	
		WHEN t2.testname = 'TEST_2' THEN t4.ts 	
		WHEN t2.testname = 'TEST_3' THEN t5.ts 		
		WHEN t2.testname = TEST_4' THEN t6.ts 			
	END AS TimeStamp, 
		t1.wafer_number,
		t2.die_id,
		t2.run_id, 
		t2.rownum, 
		t2.colnum, 
		t2.subrow, 
		t2.subcol, 
		t2.part_number, 
		t2.testname, 
		t2.set_id, 
		t2.trmode, 
		t2.channel, 
		t2.teststate, 
		t2.refstate, 
		t2.attenstate, 
		t3.Supply_Name, 
		t3.Current, 
	CASE
		WHEN t2.testname = 'TEST_2' THEN t4.INPUT / 1000000000
		WHEN t2.testname = 'TEST_4' THEN t6.INPUT / 1000000000
	END AS INPUT_1,
	CASE 
		WHEN t2.trmode = 'TX' THEN t4.TEST_2d
		WHEN t2.trmode = 'RX' THEN t4.TEST_2a
	END AS TEST_2ad,
	CASE 
		WHEN t2.trmode = 'TX' THEN t4.TEST_2a
		WHEN t2.trmode = 'RX' THEN t4.TEST_2d
	END AS TEST_2da,
	CASE
		WHEN t2.trmode = 'Tx' THEN t4.TEST_2b
		WHEN t2.trmode = 'Rx' THEN t4.TEST_2c
	END AS TEST_2cc,
		t5.ErrorCount,
	CASE
		WHEN t2.trmode = 'Tx' THEN t4.TEST_2b
		WHEN t2.trmode = 'Rx' THEN t4.TEST_2c
		WHEN t2.testname = 'TEST_1' THEN t3.Current
		WHEN t2.testname = 'TEST_3' THEN t5.ErrorCount
	END AS TestOutput,
		t6.INPUT,
		t6.TEST_4a,
		t6.TEST_4b
	FROM tst_master  t1 
		LEFT OUTER JOIN tst_dataset_header t2 
			ON  ( t2.run_id = t1.run_id )  
		LEFT OUTER JOIN tst_vector t5 
			ON  ( t5.header_id = t2.header_id )  
		LEFT OUTER JOIN tst_test_1 t3 
			ON  ( t3.header_id = t2.header_id )  
		LEFT OUTER JOIN tst_test_2 t4 
			ON  ( t4.header_id = t2.header_id )
		LEFT OUTER JOIN tst_test_4 t6 
			ON  ( t6.header_id = t2.header_id )  
	WHERE ((( t2.testname IN  ('TEST_1', 'TEST_2, 'TEST_3', 'TEST_4' ))  
		AND ( t2.teststate IN  ( '1' ,  '3', '0x0' ))))
		AND (t2.set_id NOT LIKE '1140' )
        AND (t2.set_id NOT LIKE '1240' )
		AND (t2.part_number LIKE 'WAFER_1')
 		AND (t1.wafer_number IN ( "||LotListSQLString||" ) ) // derived from the previous script you provided in your last reply
	"