topic Query Script in Discussions

Query Script

aallman — Wed, 13 Sep 2017 22:24:37 GMT

I am creating a script that starts with a modal window and runs a couple of stored procedures and automatically joins the resulting tables. These are fairly large tables, and I want to add an option for the user to run something similar to the Query Builder, where they can select columns as filters and input the data they want filtered.

How would I go about doing this before the tables are open? I want to minimize the time it takes to run the procedures and join the tables, so filtering the data after the final table is open isn't really what I want to do.

Any suggestions would be greatly appreciated!

Re: Query Script

pmroz — Thu, 14 Sep 2017 12:31:14 GMT

You can add prompts to your modal window to prefilter the data. Here's a simple example that allows a user to pre-select products and locations before the query runs. I've hardcoded the list of values to choose from, but ideally you would get this dynamically with SQL queries.

// Hardcode or get dynamically from the database
product_list = {"AAAA", "BBBB", "CCCC", "DDDD"};

// Hardcode or get dynamically from the database
location_list = {"China", "England", "Germany", "Italy", "Japan", "USA"};

nw = new window("Get Data", << modal,
	panel box("Pre-Filter",
		hlistbox(
			product_cb  = listbox(product_list),
			location_cb = listbox(location_list)
		),
	),
	panel box("Actions",
		hlistbox(
			button box("OK",
				ok_pushed = 1;
				selected_products  = product_cb  << get selected;
				selected_locations = location_cb << get selected
			),
			button box("Cancel",
				ok_pushed = 0;
			)
		)
	)
);

if (ok_pushed,
	product_in_list = "";
	location_in_list = "";

	print(selected_products, selected_locations);
	if (nitems(selected_products) > 0,
		product_in_list = "t.products IN ('" || 
			concat items(selected_products, "', '") || "')";
	);
	if (nitems(selected_locations) > 0,
		location_in_list = "t.locations IN ('" || 
			concat items(selected_locations, "', '") || "')";
	);

	print(product_in_list, location_in_list);
	// run SQL query
);

Output from log:

{"AAAA", "CCCC"}
{"England", "Germany", "Italy"}
"t.products IN ('AAAA', 'CCCC')"
"t.locations IN ('England', 'Germany', 'Italy')"

Re: Query Script

aallman — Thu, 14 Sep 2017 13:35:38 GMT

How would I get the user to select the filter? Like if I wanted to open the data tables but I only wanted where :Length was between 20-40 and :Scale was 25 nanomoles. But I want the user to be able to select which filters they want to apply from the available columns.

Re: Query Script

aallman — Thu, 14 Sep 2017 14:11:36 GMT

Would I be able to build a filter if I ran the stored procedures, got a full list of columns and their data types, and somehow used those as options? It ends up being around 45 columns, so if there is anything better please let me know!

Re: Query Script

pmroz — Thu, 14 Sep 2017 16:00:49 GMT

A general solution to this would be pretty complicated. You could build a dialog box with a fixed number of selections to make.

// Get numeric column names from sample data
dt = open("$sample_data\Boston Housing.jmp");

col_list = dt << get column names("String", numeric);
insertinto(col_list, "<no selection>", 1);

nw = new window("Get Data2",
	panel box("Filter Data",
		lineup box(ncol(3),
			text box("Column"), text box("Low Value"), text box("High Value"),
			cb1   = combo box(col_list),
			low1  = number edit box(),
			high1 = number edit box(),

			cb2   = combo box(col_list),
			low2  = number edit box(),
			high2 = number edit box(),

			cb3   = combo box(col_list),
			low3  = number edit box(),
			high3 = number edit box(),

// Add more columns if needed...
		)
	)
);