How to create user inputs for SQL where filters?

aliegner1

So I'm using an ODBC to connect to SNOWFLAKE and run some scripts. Wondering how I can set up a UI window w/ a list of WHERE filters to make it easier for ppl to use. 

 

I'm envisioning a list with popups to then map to a variable to feed into the SQL script. possible?

 

 

 

//**SNOWFLAKE QUERY****************************************************************************************************
uid = Get Environment Variable( "USERNAME" );
sql_state="
/********************************/

SELECT distinct
column1
,column2
,etc

from Database
 
WHERE 1=1
and filter1 like '%qwerty%'
and filter2 in ('a', 'b', 'c')
--and filter3 = '54168'
--and filter4 <> 'that_thing' 
--and filter5 not in ('group1','group2')
and Date >= CURRENT_DATE - 30 /*go back 30 days*/
";

snowflake_spc = Open Database( "DSN=SNOWFLAKE1;UID=account;PWD=password;", sql_state );
/****************************************************************************************************/

 

jthi

This is definitely possible. Depending a lot on your coding skills with jsl and how complicated the system will be (if it will be just where filtering with simple where clauses, it could be quite simple to do).

 

Couple of things to think about:

1. Use template string and then Eval Insert to add values there (or just concatenate strings together).
2. How do you want to handle like/=? Display Boxes (radio box, check box, combo box) or maybe some specific character in the Text Edit Box which would indicate how to parse the text?
3. How to handle in and multiple like conditions (filter1 like something or filter2 like something)?
4. How to handle not?
5. Modal window gives you easy way to handle the flow of script
6. Think about how the system might be expanded later
7. Scripting Guide and Scripting Index will be a great help. Also JMP Help will help with different Display Boxes.

Below is very simple example on using template string and then using eval insert to build the final SQL.

 

Names Default To Here(1);
sqlTemplate = "\[
select * 
from * 
where 1=1
^filter1Str^
^filter2Str^
]\";

filter1Str = "";
filter2Str = "";
rbSel = {"like", "in", "=", "not in", "not like"};
ex = New Window("Dialog() example",
	<<Modal,
	<<Return Result,
	V List Box(
		H List box(TextBox("filter1: "), filter1txt = Text Edit Box(), filter1rb = Radio Box(rbSel)),
		H List box(TextBox("filter2: "), filter2txt = Text Edit Box(), filter2rb = Radio Box(rbSel)),
		H List Box(Button Box("OK"), Button Box("Cancel"))
	)
);

If(ex["filter1txt"] != "",
	filter1Str = "and filter1 " || rbSel[ex["filter1rb"]] || " " || ex["filter1txt"];
);

If(ex["filter2txt"] != "",
	filter2Str = "and filter2 " || rbSel[ex["filter2rb"]] || " " || ex["filter2txt"];
);

finalSql = Trim Whitespace(Eval Insert(sqlTemplate));
Write(finalSQl);

 

I have built one quite similar system, which builds complete SQL-queries depending on user inputs and choices in an user interface (and I'm currently doing full rewrite on that when I have time, because expanding it got too difficult).