How to pass a date to an SQL Query in JMP

[#1]

I’m getting a data sheet via SQL into JMP. One column is a :Date column. I’d like to pick the last date (Max value) or "last date-1 day" from this :Date column and transfer this date into an update querry, where CustomSQL( “ … Date >= ...) is.

[#2 · Accepted solution]

Hi Thomas,

here's a small script, that may perform your task,

the first part is only to create a sample table (you already may have),

the second is the important one for you, and you can change to your needs.

To better understand you can execute it line by line, and have a look at the log to see what happens.

Georg

// 1st part, generate table
// make a table and fill it with some data
dt = New Table( "Query Data",
	Add Rows( 1e2 ),
	New Column( "Wafer", set each value( Row() ) ),
	New Column( "Machine", Character, set each value( If( Row() < 50, "A", "B" ) ) ),
	New Column( "date",
		Numeric,
		Format( "d.m.y", 10 ),
		Input Format( "d.m.y" ),
		set each value( Date Increment( Date DMY( 19, 8, 2019 ), "Day", -1 * Row() ) )
	),
	New Column( "value", set each value( Random Normal( 100, 10 ) ) ), 
);

// add a script (oracle sql query with date placeholder &DATE )
dt << New Property( "MySQLQuery", "select * from some_table where date > to_date('&DATE', 'dd.mm.yyyy') " );

// 2nd part, perform task

// get max date
max_date = Col Max( dt:date );
Show( Day( max_date ), Month( max_date ), Year( max_date ) );

// modify and convert max date to string
my_query = dt << get Property( "MySQLQuery" );
date_string = Right( "0" || Char( Day( max_date ) - 3 /* subtract some days */ ), 2 ) || "." || Right( "0" || Char( Month( max_date ) ), 2 ) || "."
 || Char( Year( max_date ) );
Show( date_string );

// replace query with changed date in table
Substitute Into( my_query, "&DATE", date_string );
dt << set property( "MySQLQuery", Eval( my_query ) );