cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
Browse apps to extend the software in the new JMP Marketplace
Choose Language Hide Translation Bar
Franck_R
Level III

SQL Custom and Eval / Parse (and \!\!\!...)

Hi!

I do a lot of queries that can't use the JMP query builder and have to be written in SQL Custom.
But as I also want to generalize my code I have to integrate a lot of eval / inserts and \! 

Here is an example:

 

Code not yet generalized

New SQL Query(
	Connection(
		"ODBC:blabla"
	),
	QueryName( "v_test_run" ),
	CustomSQL(
		"SELECT \!"t1\!".lot_id 
		FROM \!"public\!".\!"v_test_run\!"  \!"t1\!" 
		WHERE  (  (  ( \!"t1\!".test_run_timestamp >= '2020-11-01 15:25:11' )  )  ) ;"
	)
) << Modify;

Code Generalized to include a date:

gbStr = Eval Insert("

New SQL Query(
	Connection(
		\!"ODBC:blabla\!"
	),
	QueryName( \!"v_test_run\!" ),
	CustomSQL(
		\!"SELECT \!\!\!"t1\!\!\!".lot_id 
FROM \!\!\!"public\!\!\!".\!\!\!"v_test_run\!\!\!"  \!\!\!"t1\!\!\!" 
WHERE  (  (  ( \!\!\!"t1\!\!\!".test_run_timestamp >= '^date_deb^' )  )  ) ;\!"
)
) << Modify;


");
Eval( Parse( gbStr ) );

Is there any other method that would save me from having to juggle with these \!\!\! symbols?

On complex scripts it's really difficult to handle and debug!

 

Thank you

2 ACCEPTED SOLUTIONS

Accepted Solutions
ih
Super User (Alumni) ih
Super User (Alumni)

Re: SQL Custom and Eval / Parse (and \!\!\!...)

There are a few options outlined in this article, I recommend starting with Eval( Eval Expr()) and then wrapping each piece you need to replace with Expr(), and using \[ ... ]\ to reduce the number of escaped characters.  It could look something like this:

Eval( Eval Expr(
	New SQL Query(
		Connection(
			"ODBC:blabla"
		),
		QueryName( "v_test_run" ),
		CustomSQL(
			Expr( "\[SELECT "t1".lot_id 
			FROM "public"."v_test_run"  "t1" 
			WHERE  (  (  ( "t1".test_run_timestamp >= ']\" || date_deb || "' )  )  ) ;")
		)
	) << Modify
) );

 

View solution in original post

Georg
Level VII

Re: SQL Custom and Eval / Parse (and \!\!\!...)

I like to follow a different path avoiding unintuitive eval parse combinations, where I find them quite useful for other purposes.

Often I have complex queries (built in plain SQL by other Tools like SQL developer). And I Need to modify some variables.

 

The attached example Shows how it works with big class datatable, howewer it would work for all ODBC Connections in a similar way.

Simply I prepare my Connection string (not used here) and the SQL Query in a string variable, given to the "New SQL Query".object.

I use "assign" (instead of "=") because it works nice for large queries and Code folding.

And I'm more flexible for also using open database function, or processing the SQL in other ways.

Usually the double quotes are not needed in a plain SQL (and I try to avoid also), unless you have names with spaces etc. like "Big Class".

 

Names Default To Here( 1 );

cdt = Open( "$Sample_Data\Big Class.jmp" );
age = "15";

// define custom SQL with placeholder
Assign( sql_str, "SELECT t1.name, t1.age, t1.sex, t1.height, 
	t1.weight 
FROM \!"Big Class\!"  t1 
WHERE  (  (  ( t1.age IN  ( &age )  )  )  ) ;" );

// fill placeholder
Substitute Into( sql_str, "&age", "15" );

// execute query
dt_age = New SQL Query( Version( 130 ), Connection( "JMP" ), JMP Tables( cdt ), QueryName( "aged" || "_" || age ), CustomSQL( sql_str ) ) << Run;
Georg

View solution in original post

13 REPLIES 13
ih
Super User (Alumni) ih
Super User (Alumni)

Re: SQL Custom and Eval / Parse (and \!\!\!...)

There are a few options outlined in this article, I recommend starting with Eval( Eval Expr()) and then wrapping each piece you need to replace with Expr(), and using \[ ... ]\ to reduce the number of escaped characters.  It could look something like this:

Eval( Eval Expr(
	New SQL Query(
		Connection(
			"ODBC:blabla"
		),
		QueryName( "v_test_run" ),
		CustomSQL(
			Expr( "\[SELECT "t1".lot_id 
			FROM "public"."v_test_run"  "t1" 
			WHERE  (  (  ( "t1".test_run_timestamp >= ']\" || date_deb || "' )  )  ) ;")
		)
	) << Modify
) );

 

Franck_R
Level III

Re: SQL Custom and Eval / Parse (and \!\!\!...)

Thank you very much! I had read this article and had tried but I still have difficulties in mastering this eval (eval expr (expr))

ih
Super User (Alumni) ih
Super User (Alumni)

Re: SQL Custom and Eval / Parse (and \!\!\!...)

I agree that this syntax is only obvious once you understand it, and even then it is confusing.  Check out this wish list item to introduce an Eval Expr First() function, maybe that would be more intuitive?

pmroz
Super User

Re: SQL Custom and Eval / Parse (and \!\!\!...)

I would use the delimiters \[ and ]\ to include double quotes in the string.  

date_deb = "to_date('01-Mar-2019', 'dd-mon-yyyy')";
gbStr = Eval Insert("\[
New SQL Query(
	Connection(
		"ODBC:blabla"
	),
	QueryName( "v_test_run" ),
	CustomSQL(
		"SELECT "t1".lot_id 
FROM "public"."v_test_run"  "t1" 
WHERE  (  (  ( "t1".test_run_timestamp >= ^date_deb^ )  )  ) ;"
)
) << Modify;
]\");

write(gbstr);

Eval( Parse( gbStr ) );

Output:

New SQL Query(
	Connection(
		"ODBC:blabla"
	),
	QueryName( "v_test_run" ),
	CustomSQL(
		"SELECT "t1".lot_id 
FROM "public"."v_test_run"  "t1" 
WHERE  (  (  ( "t1".test_run_timestamp >= to_date('01-Mar-2019', 'dd-mon-yyyy') )  )  ) ;"
)
) << Modify;
Georg
Level VII

Re: SQL Custom and Eval / Parse (and \!\!\!...)

I like to follow a different path avoiding unintuitive eval parse combinations, where I find them quite useful for other purposes.

Often I have complex queries (built in plain SQL by other Tools like SQL developer). And I Need to modify some variables.

 

The attached example Shows how it works with big class datatable, howewer it would work for all ODBC Connections in a similar way.

Simply I prepare my Connection string (not used here) and the SQL Query in a string variable, given to the "New SQL Query".object.

I use "assign" (instead of "=") because it works nice for large queries and Code folding.

And I'm more flexible for also using open database function, or processing the SQL in other ways.

Usually the double quotes are not needed in a plain SQL (and I try to avoid also), unless you have names with spaces etc. like "Big Class".

 

Names Default To Here( 1 );

cdt = Open( "$Sample_Data\Big Class.jmp" );
age = "15";

// define custom SQL with placeholder
Assign( sql_str, "SELECT t1.name, t1.age, t1.sex, t1.height, 
	t1.weight 
FROM \!"Big Class\!"  t1 
WHERE  (  (  ( t1.age IN  ( &age )  )  )  ) ;" );

// fill placeholder
Substitute Into( sql_str, "&age", "15" );

// execute query
dt_age = New SQL Query( Version( 130 ), Connection( "JMP" ), JMP Tables( cdt ), QueryName( "aged" || "_" || age ), CustomSQL( sql_str ) ) << Run;
Georg
Franck_R
Level III

Re: SQL Custom and Eval / Parse (and \!\!\!...)

Great, that's really very useful! And since we've just created the database and I was the main actor of this creation, I've just made sure that no table name or variable name has parentheses or spaces, so it should simplify this generalisation.

ReliabilityWolf
Level III

Re: SQL Custom and Eval / Parse (and \!\!\!...)

@pmroz I tried the similar thing as above. after custom SQL run, returning the result  is 1 in the end of log. could you please help explain more about the result of 1? is it Eval( Parse( gbStr ) ) return code that you mentioned in your script? 

ReliabilityWolf_0-1719413439347.png

 

pmroz
Super User

Re: SQL Custom and Eval / Parse (and \!\!\!...)

Sorry I don't use Query Builder.  I would guess that the return value of 1 is true indicating success.  But that could be a wild surmise

ReliabilityWolf
Level III

Re: SQL Custom and Eval / Parse (and \!\!\!...)

@pmroz thanks for your reply. May I have your support to review the following script? 

SQL Query usually takes a few minutes to access database. deleting rows (if :rank >1) sometimes doesn't execute but can do manually. I have no idea.... not sure if my script has any problem? 

 

date_deb = "to_date('01-Mar-2019', 'dd-mon-yyyy')";
gbStr = Eval Insert("\[
dt1 = New SQL Query(
	Connection(
		"ODBC:blabla"
	),
	QueryName( "v_test_run" ),
	CustomSQL(
		"SELECT "t1".lot_id 
FROM "public"."v_test_run"  "t1" 
WHERE  (  (  ( "t1".test_run_timestamp >= ^date_deb^ )  )  ) ;"
)
) << Run foreground;
]\");

write(gbstr);

Eval( Parse( gbStr ) );

dt1 << New Column( "Rank", Numeric, "Continuous", Format( "Best", 12 ) );
dt1:Rank << set Formula( Col Rank( :DATE* -1, :ID ) );
dt1 << Row Selection(select where(:Rank > 1 ));
dt1 << delete rows;