Subscribe Bookmark RSS Feed

How to use the global variables in matrix or string format

judith

Community Trekker

Joined:

Jan 19, 2012

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?

4 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

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");

judith

Community Trekker

Joined:

Jan 19, 2012

Thanks for your help. That is every helpful.

One more concern now is in my code I also need to add GROUP BY in the end. For instance, GROUP BY WEIGHT. How to put 'group by' in this case?

Thanks again.

pmroz

Super User

Joined:

Jun 23, 2011

You can only use the GROUP BY statement if you are grouping the data somehow, i.e. with a Count(*) or SUM() etc.  Did you perhaps mean ORDER BY?  Just tack it to the end with the || concatenation operator.

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

If your sort key is a variable again just tack it to the end:

order_by = "WEIGHT";

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

judith

Community Trekker

Joined:

Jan 19, 2012

Yes, ORDER BY works too.

My goodness, you solved my problem, which puzzled me for weeks and dragged my project. Thanks again for your help.