cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Thomas1
Level V

How to pass a date to an SQL Query in JMP

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.
1 ACCEPTED SOLUTION

Accepted Solutions
Georg
Level VII

Re: How to pass a date to an SQL Query in JMP

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

View solution in original post

1 REPLY 1
Georg
Level VII

Re: How to pass a date to an SQL Query in JMP

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