Dynamic Filters for SQL Query

[#1]

So I dont know anything really about SQL, so I used the JMP Query Builder and based all my code off that.

Basically, I want to allow the user to filter any data they want via a pop up screen where they can pick the column to filter, then what values they want to filter.  All the columns in my JMP table are valid column names in my  Database.  So i got to the point where i created an associative array that holds all the filters and values the user chose where the keys are the filters.

The problem i have is in the New SQL Query where i can use "Where" to do the actual filtering, im not sure how i can add my associative array in.

I'm sure there is a better way to do this.  Any thoughts?

_dt = Current Data Table();
selection = Associative Array();
//Create a window to collect filter information
TestIdWindow = New Window( "Filters Window",
	<<Modal,
	vlb = V List Box(
		hlb2 = H List Box( _cbFilters = Combo Box( _dt << Get Column Names( String ) ) ),
		hlb3 = H List Box( filtersTextEditBox = Text Edit Box( "", <<SetWidth( 500 ), <<Set Wrap( 480 ) ) ),
		FilterTitle = Text Box( "Current Filters" ),
		FilterBox = Text Box( "" ),
		Button Box( "Add Filter",
			totalText = "";
			selection[_cbFilters << Get Selected()] = filtersTextEditBox << Get Text();
			If( Not( Is Missing( Num( selection[_cbFilters << Get Selected()] ) ) ),  //Forces any numerical data to be a number
				selection[_cbFilters << Get Selected()] = Num( filtersTextEditBox << Get Text() )
			);
			currentkey = selection << First;
			For( x = 1, x <= N Items( selection ), x++,  //Used to print the current filters to the window
				totalText = totalText || currentkey || ": " || Char( selection[currentkey] ) || ",";
				nextkey = selection << Next( currentkey );
				currentkey = nextkey;
			);
			FilterBox << Set Text( totalText );
		),
		Button Box( "OK" ), 

	), 

);
currentkey = selection << First;
testVar = {};
For( x = 1, x <= N Items( selection ), x++,
	If( Is List( selection[currentkey] ),
		,
		selection[currentkey] = Eval List( {selection[currentkey]} )
	);
	Insert Into(
		testVar,
		Expr(In List(
			Column( currentkey, "t1" ),
			selection[currentkey],
			UI( SelectListFilter( ListBox, Base( "Categorical" ) ) );
				
		)
		)
	);
			
	currentkey = selection << Next( currentkey );
);
New SQL Query(
	Version( 130 ),
	Connection(
		"Some Server"
	),
	QueryName( "QueryName" ),
	Select(
		Distinct,
		Column( "Column1", "t1" ),
		Column( "Column2", "t1", Analysis Type( "Nominal" ) ),
		Column( "Column3", "t1" ),
	),
	Where(
		Eval(testVar[1]);
	), 
	
) << Run Foreground( UpdateTable( Current Data Table() ) );

[#2]

First try running your SQL query with all the filters applied manually to the columns the user will pick from. This will generate the required where clause code into which you can put lists of values chosen by the user.

[#3]

I know what the clause code is, but the user may not want to do a filter on every column just certain ones.  Is it okay for some of the clause codes to compare to nothing?

Also, there may near a hundred columns, i dont want to have to explicitly add a clause code for each column.

I did test that with a couple columns, but im trying to build this where clause dynamically.  I am taking some precautions in my code to force all the clauses to be categorical... but i just need to figure out how to do the where clause with the In Lists dynamically.

Where(
		In List(
			Column( "Column 1", "t1" ),
			{"some value 1",
			"some value 2"},
			UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
		) & In List(
			Column( "Column 2", "t1" ),
			{Some value 3, some value 4},
			UI( SelectListFilter( ListBox, Base( "Categorical" ) ) )
		)
	)

[#4]

There is a feature for when using categorical columns in your SQL called _AllRows_ which you can invoke if the user makes no choice:

//window for selection
// get data
PickFilter = Function( {chosenCostCentre},
	//{Default Local}, 
	
	//deal with user making no choice
	If(NItems(chosenCostCentre[1])==0,
		chosenCostCentre[1] = "_AllRows_";
	);
...

Then using dynamic JSL you can insert this text in place of the list if there is no list:

...
//get report names from filter table
Eval(
		Parse(
			Eval Insert(
				"\[		
	dtFilterNames = New SQL Query(
		Connection( connectionString ),
		QueryName( "RvwReports" ),
		Select( Column( "ReportID", "t1" ), Column( "ReportName", "t1" ) ),
		From( Table( "RvwReports", Schema( "dbo" ), Alias( "t1" ) ) ),
		Where(
 In List(
				Column( "CostCentreCommon", "t1" ),
				^chosenCostCentre[1]^,
				UI( SelectListFilter( CheckBoxList, Base( "Categorical" ) ) )
			) 
			
		)
	) << Run Foreground( );
	
	          			 ]\"
			)
		)
	);

Equally using dynamic JSL you can write each clause as a seperate string, with the default set to clause=""; and only update them if the user picks that column from an initial list of columns to filter on.

At this point if you aren't limiting the user by pre-deciding what the 5-12 most important columns are, then why aren't they using the built-in JMP query builder and you just write the post-SQL JSL?

[#5]

Thanks stephen.  Let me give your suggestion a try today.  I didnt think about creating the entire SQL Query as a string, just the Where Portion.

As for your comment about just having 5-12 options to filter, that was actually the initial goal.  But then I couldnt decide what 5-12 things I think all the users would ever want to filter on, so I thought well, if i can already do a few of them statically, how hard would it be to make it dynamic.  Then it blew up and here I am :)