How to use the global variables in matrix or string format

judith

How to use the global variables in matrix or string format? For instance, the following codes worked fine for one value of gender:
 
Unlock Globals (openc);
openc=Function({gen},
::gen="F";
 
new=Open Database("DSN=Excel Files;DBQ=c:\users\xxu\documents\big class.xlsb;
;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;",
"SELECT * FROM Big_Class WHERE Big_Class.sex = '"||(::gen)||"'",
"New"
)
);
 
lock globals(openc);
 
openc(gen);
 
Now I need to replace the global GEN by two values F and M, i.e. ::gen=”F, M”; How the Where clause in Select statement identify it as the two values instead of one string value? So how to modify the codes? I tried parse function and it didn’t work.
 
Unlock Globals (openc);
openc=Function({gen},
::gen="F, M";
 
new=Open Database("DSN=Excel Files;DBQ=c:\users\xxu\documents\big class.xlsb;
;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;",
"SELECT * FROM Big_Class WHERE Big_Class.sex IN ('"||(::gen)||"')",
"New"
)
);
 
lock globals(openc);
 
openc(gen);
 
The above codes have been modified for two values of Gen, but it didn’t work. Did you have any experience on the multiple values of the variable in where clause? Or do you have any recommendation?

pmroz

This should do what you want.  I would change your list of values to search for to be a real JMP list.  Then use the CONCAT ITEMS function to convert it to an IN list.

my_list = {"F", "M"};

in_list = "('" || Concat Items(my_list, "','") || "')";

sql_string = "SELECT * FROM Big_Class WHERE Big_Class.sex IN " || in_list;

new = Open Database("DSN=Excel Files;DBQ=c:\users\xxu\documents\big class.xlsb;

;DriverId=1046;MaxBufferSize=2048;PageTimeout=5;",

      sql_string, "New");