Generate data tables grips variable with a loop

Question

Hi all,

I wrote the loop to Execute SQL() data table with multiple table name. For the management of the data table, I am trying to create a grip for each table but it failed.

Need everyone help on the code as below

sql_code 1 = Expr( "..." );
sql_code 2 = Expr( "..." );
sql_code 3 = Expr( "..." );
list = { "sql_code 1", "sql_code 2", sql_code 3" };
For( k = 1, k <= N Items( list ), k++, 
sub_list = list[k];
Eval list( Parse( "var_" || sub_list )  ) = Execute SQL( database_connection, Eval(Parse(list)), invisible, "Data Table_" || sub_list );
);

Have a good day

gzmorgan0 · Accepted Answer

@Black_Wheel,

There were a several issues with your script.

* The actual SQL should be a string/character variable.
* The last item in list is missing a quote.
* Missing concepts in the command line that you are trying to generate within  the For-loop

The left hand side of a JMP assignment needs to be an L-Value . In essence, an L-Value must be a word or a name not an expression.

However, I believe you are trying to create a JMP command where the left side of the assignment (the left side of = ) is a name.

Below is from the JMP Log window

Parse(Eval Insert( "\[var_^sub_list^  = Execute SQL( database_connection, ^list[k]^, invisible, "Data Table_^sub_list^")]" )) ;

/*:

var_sql_code 1 = Execute SQL(
	database_connection,
	sql_code 1,
	invisible,
	"Data Table_sql_code 1"
)

The top line generates a valid JMP expression. If that is used as the argument for the Eval() function, and your Database connection string and your SQL code has no errors, then the query should work should work.  See the script below.

To help you understand the JSL functions, from the JMP  main menu > Help > Scripting Index > Functions, look up:

* Eval Insert( )
* Parse( )
* Eval( ), and 
* the syntax for Execute SQL( )

sql_code 1 = "..." ;
sql_code 2 = "..." ;
sql_code 3 = "..." ;
list = { "sql_code 1", "sql_code 2", "sql_code 3" };
For( k = 1, k <= N Items( list ), k++, 
sub_list = list[k];
Eval(Parse(Eval Insert( "\[var_^sub_list^  = Execute SQL( database_connection, ^list[k]^, invisible, "Data Table_^sub_list^")]" )) ) ;
);

Hope that helps. Good Luck!

pmroz · Accepted Answer

Rather than go through all the machinations to create a dynamic variable name for each of your SQL result tables, why not just create a list of tables?  Especially if your SQL statements are complex.

sql_code 1 = Expr( "..." );
sql_code 2 = Expr( "..." );
sql_code 3 = Expr( "..." );
list = { "sql_code 1", "sql_code 2", "sql_code 3" };
dt_list = {};
For( k = 1, k <= N Items( list ), k++, 
	sql_statement = list[k];
	dt_list[k] = Execute SQL( database_connection, sql_statement, invisible, "Data Table_" || char(k) );
);

If you want the SQL statement to be a part of the newly created tables, you could add it as a table variable.