Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
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
Highlighted
Georg
Level IV

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
Highlighted
Georg
Level IV

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

Article Labels

    There are no labels assigned to this post.