more complex data base queries

[#1]

Is there a trick to set up more complex data base queries via the Query Builder GUI?

e.g. 

- more complicated structure than just 1 primary data table, joined with several secondary data tables - on one level.

- use calculated / aggregated values instead of already existing entries as matches to join "on".

[#2]

Hi @hogi ,

I don't think these will be possible in Query Builder. It is really intended to help people get started with querying databases without having to learn any SQL. It's not going to cover every possible level of complexity of SQL query.

I think you would have to learn some SQL to do this. Or change the database schema to enable the same extract with a simpler query. Or bring the tables into JMP and do the calculations and joins on JMP tables, which you could automate through scripting.

Sorry if that is not the answer that you were hoping for but maybe it helps.

Phil

[#3]

Ok, then I did not miss a cool feature

For debugging of SQL queries in JSL Editor it really helps that via double click on a bracket the code between the 2 corresponding brackets gets highlighted *).
But I I really miss the feature other editors provide for editing SQL code, like displaying corresponding brackets with different colors or autosuggestions.

*) If Jmp Dev Team starts to work on optimized syntax highlighting in JSL editor, it would be great to also consider the application  of the feature for SQL code in Database Query Strings.

[#4 · Accepted solution]

If you need to develop more sophisticated SQL queries I would strongly suggest using a tool like PL/SQL Developer, TOAD, SQL Developer etc. for Oracle.  Once your queries are debugged bring them into JMP and run them via execute SQL.

[#5]

I'd like to re-iterate the request for calculated values in a join.

Yes, it can be done in a different SQL editor, or by changing the Query Builder to Custom SQL - but there's more power to Query Builder than just easy query construction.  It provides a fast and easy way to create a pretty complicated filter dialog.  I could edit the custom SQL to join upper(tb1.field) on upper(tb2.field), or add TO_NUMBER or CAST  in the SELECT - but then I loose all the filter GUI portion.   I could build an app to do it - but that seem like reinventing what's already there. 

For that matter, TO_NUMBER isn't in the calculated field options.

 Charles