242411_en-US

liliconcom

Generate data tables grips variable with a loop

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

 

 

Re: Generate data tables grips variable with a loop

@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!

 

Re: Generate data tables grips variable with a loop

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.

Re: Generate data tables grips variable with a loop

@Black_Wheel ,

 

I  fully agree with Pete, @pmroz. A list of expressions, a list columns/variables, a list of data table names or references, matched by list item order (k), is code that is easier to read and maintain and I recommend you use it.

 

Re: Generate data tables grips variable with a loop

Yeah. @pmroz @gzmorgan0  definitely a list is better than just parsing some string into a variable.  

Unless you're doing the same thing to all three tables though, I wouldn't even put them in a list.  I'd just name the variables with descriptive names.  The second example is less lines, easier to understand, and gives you named variables so  you don't have to remember which table is 1, 2, and 3.  

Names default to here(1);
sqllist = {
 "SELECT * 
 FROM People", 
 "SELECT * 
 FROM Places", 
 "SELECT * 
 FROM Things"
};
table_names = {"People", "Places", "Things"};
dt_list = {};

for(i=1, i<=nitems(sqllist), i++, 
 dt_list[k] = Execute SQL(dbc, sqllist[i], table_names[i])
);

// vs 

dt_people = Execute SQL(dbc, 
 "SELECT * 
 FROM People", 
 "People"
);

dt_places = Execute SQL(dbc, 
 "SELECT * 
 FROM Places", 
 "Places"
);

dt_things = Execute SQL(dbc, 
 "SELECT * 
 FROM Things", 
 "Things"
);

If the tables all have the same schema  though, I'd just have a list of sql statements in a list and call that directly.