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

[#1]

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]

I think formula evaluation takes some time,

a wait(0) would force JMP to finish until row selection is done (see script) and deletion can be done.

I would really encourage you to keep the string part as small as possible, as it is always error prone and difficult to read.

As well it is a great possiblity to use JMP Tables to figure out these kind of problems, and  to write scripts for the community that everyone can run out of the box.

Edited: replaced "\C:\Program Files\JMP\JMPPRO\18\Samples\Data" by "$SAMPLE_DATA"

Names Default To Here( 1 );

// date_deb = "to_date('01-Mar-2019', 'dd-mon-yyyy')";

date_str = "2008-06-19";

/*
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;
]\");
*/

dt1_sql = Eval Insert(
	"\[SELECT t1.Date, t1.State, t1."Flu Cases", t1.Region, 
	t1."Population (July 2009)" 
FROM "Seasonal Flu"  t1 
WHERE  (  (  ( t1.Date >= JMPDATE('^date_str^') )  )  ) ;]\"
);

dt1 = New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables( ["Seasonal Flu" => "$SAMPLE_DATA\Seasonal Flu.jmp"] ),
	QueryName( "SQLQuery1" ),
	CustomSQL( dt1_sql )
) << run;

// Write( gbstr );
// Eval( Parse( gbStr ) );

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

[#3]

@Georg thanks for suggestion. it's really a good and smart idea for keeping the string part as small as possible. i tried it, and deletion still fail with wait(0). but it works for wait(1). however, when I access more size data from SQL datacenter (likely it takes a few mins to fetch data from server),  it works until wait(5). my guess is col rank() usually takes some time to calculate. more data size, more time for calculation.  when row selection execute before col rank calculation finish, deletion will fail. is there any way to determine the calculation complete? if yes, I can use IF function to run next step.      

[#4]

@ReliabilityWolf generally I would try to do the filtering at database, so it depends on the sql language you have how to do (you can use rank with subselect in sql as well). And you gain a lot of time for unnecessary traffic.

Additionally I would avoid using a formula for this, if you want to have unique values for the ID, you can sort in SQL and use select duplicate rows and delete, see below. Database usually is much faster doing these tasks than your client.

Regarding formulas, if you need, perhaps the table message "run formulas" or "rerun formulas" helps. Or you can delete formula, or write the results directly into the column ...

Names Default To Here( 1 );

date_str = "2008-06-19";

dt1_sql = Eval Insert(
	"\[SELECT t1.Date, t1.State, t1."Flu Cases", t1.Region, 
	t1."Population (July 2009)" 
FROM "Seasonal Flu"  t1 
WHERE  (  (  ( t1.Date >= JMPDATE('^date_str^') )  )  )
order by
t1.State, t1.date desc;]\"
);

dt1 = New SQL Query(
	Version( 130 ),
	Connection( "JMP" ),
	JMP Tables( ["Seasonal Flu" => "$SAMPLE_DATA\Seasonal Flu.jmp"] ),
	QueryName( "SQLQuery1" ),
	CustomSQL( dt1_sql )
) << run;

dt1 << Select Duplicate Rows( Match( :State ) ) << delete rows();

[#5]

really appreciate your new direction on filtering at database. let me take a look at this. thanks again.