How to Translate JMP-where-clause from data filter to SQL-where-clause

[#1]

Dear Community,

I'd like to use the data filter object to let users filter some data to be queried from Oracle database via SQL.

So I extracted a small part from data into a JMP table,

and put a data filter over it.

From Data Filter I can extract the where clause,

it may look as follows:

Select Where(
	(:PC == "A_PROCESS" | :PC == "C_PROCESS" | :PC == "D_PROCESS") & :TC == "01"
	 & (:POS >= 25 & :POS <= 126)
)

and I would like to transfer this into an SQL Where-Clause like:

where 
PC in ("A_PROCESS", "C_PROCESS", "D_PROCESS")
and TC == "01"
and POS between 25 and 126

As I'm not so familiar with text processing,

is there an easy way how to do this in JMP,

especially how to disassemble the JMP-where-clause (that may be more complex than only concatenated And's),

and to re-assemble it in an SQL-where-clause?

Thanks for any hint, best regards,

Georg

[#2 · Accepted solution]

Dear all,

meanwhile I solved the issue. I realized, my script has not to understand the JMP-where clause, but simply should translate it to a working SQL-where clause.

So here is an example JSL for doing this.

// Example for translating a JMP-where-clause from e.g. Data Filter to a SQL-where-clause
JMP_where_clause =
"Select Where(
	(:PC == \!"A_PROCESS\!" | :PC == \!"C_PROCESS\!" | :PC == \!"D_PROCESS\!") & :TC == \!"01\!"
	 & (:POS >= 25 & :POS <= 126)
)";

	jmp2sql_where = Function( {jmp_where}, 
	// remove "Select Where()" from the string
		df_list = Regex Match( jmp_where, "Select Where\((.*)\)" );
	// replace JMP keywords by SQL keywords
		sql_where = "AND " || Substitute( df_list[2], "\!"", "'", "==", "=", ":", "CZIM.", "&", "AND", "|", "OR" );
		Return( sql_where );
	);
	sql_where = jmp2sql_where( JMP_where_clause);

show(sql_where);

And the result is:

sql_where = "AND 
	(CZIM.PC = 'A_PROCESS' OR CZIM.PC = 'C_PROCESS' OR CZIM.PC = 'D_PROCESS') AND CZIM.TC = '01'
	 AND (CZIM.POS >= 25 AND CZIM.POS <= 126)
";

Best regards,

   Georg

[#3]

Hey, Georg,

Have you looked at Query Builder at all in JMP?  File > Database > Query Builder.  It allows you to build queries interactively against ODBC data sources, including filtering, included filters that prompt, and then you can either run the queries interactively or use them in JSL.  It's a lot less time-consuming than writing your own SQL by hand for most people (but if you want to write your own SQL to use with Query Builder, that is also an option).

Thanks,

Eric

[#4]

Hey Eric,

thanks for your answer, yes, I had a look at Query Builder.

I use the Query Builder in several scripts etc., but I always do not use the dialog, but only the custom sql method.

This is for two reasons:

• our databases are rather complex (many schemes, tables etc.), so it needs a lot clicking before anyone can find the right table
• the documentation / saved queries in data table are looking quite ugly, when you have clicked the table together instead of using custom sql,
  so it can not be used again ...

So I like to work with "New SQL Query" + custom SQL, especially to use the generated tables by that way (e.g. update from database and do the same analysis again ...).

My target is to build a user interface for querying very complex data,

the first part is done at database (client with jmp cannot do by reason of performance),

and the second part is done by jmp starting with getting the data from Database by an easy to use customized GUI.

Here JMP offers perfect tools, but sometimes there remains a small problem to solve, like for the where clause.

Best regards,

   Georg

[#5]

Hey Eric,

your comment brings me to another question,

as we have very complex databases with many schemes and tables,

does the JMP Query Builder allow one to start it user defined?

I.e. can I start the Query builder with predefined schemes and filtered tables,

so that I do not need several minutes to click through the database?

Best regards,

   Georg

[#6 · Accepted solution]

Hey, Georg,

Great question.  Here is what I do:

1. Launch Query Builder and connect to the database.
2. Use the Select Tables for Query dialog to add whatever tables and join relationships that you expect to use frequently.
3. Click Build Query.
4. When the Query Builder window comes up, don't add any columns or filters to the query.  Instead, immediatly click Save and give the query a name like complex_query_template.jmpquery.
5. When you want to build a query using that template as a starting point, find complex_query_template.jmpquery in the Home window Recent Files list, right-click on it and select Edit a Copy.  This will launch Query Builder with all the tables and joins in place as the template defined, and then you can proceed to select columns and add filters.

If you template query is not in your Home Window Recent Files list, you can use File Open to open it, but you just have to remember to immediately do a Save As so that after you add stuff to the query and save it, you don't overwrite your template.

HTH,

Eric

[#7]

Hey Eric,

thanks for the quick response, it helped,

and Query Builder has some very nice features. So I will look, where to use.

I could save the query with defined joins,

and use it again.

But a kind of filter of tables (stopping and saving Query at joining step) was not possible,

and I found not the possibility to connect filters with AND instead of OR.

Best regards,

   Georg

[#8 · Accepted solution]

Hey, Georg,

I'm not sure I follow this statement:  "But a kind of filter of tables (stopping and saving Query at joining step) was not possible."

On the filters, AND is implicit.  I can see how that is confusing.  But if you just add a bunch of filters without clicking that OR button, you get AND.  In this query, for example:

I dragged Rating from the Available Columns list over into the Filters list, and then I dragged DaysOut, and then I dragged State.  I never clicked the OR button.  So those three filters are AND'd together - that filter is (Rating = 'PG' AND DaysOut >= 5 AND State = 'FL').  So, you can have a single group of AND'd filters, or you can have multiple single filters that are OR'd together, or you can have groups of AND'd filters that are OR'd with each other.  The only think you can't have is a group of filters that is OR'd together that is AND'd with another group of filters that are OR'd together.  

Ultimately, if you can't get all the way to what you need with Query Builder, you can get as far as it will take you, and then use the red triangle menu at the upper left and select Convert to Custom SQL Query.  That will generate the SQL for the query as you have built it interactively and put it in a text editor so that you can edit it by hand to get what you need:

Once you make changes in Custom SQL mode, you can't really go back to interactive mode.

HTH,

Eric