topic Re: more complex data base queries in Discussions

more complex data base queries

hogi — Thu, 09 Nov 2023 19:50:15 GMT

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".

Re: more complex data base queries

Phil_Kay — Fri, 10 Nov 2023 09:03:13 GMT

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

Re: more complex data base queries

hogi — Fri, 10 Nov 2023 10:02:00 GMT

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 , it would be great to also consider the application of the feature for SQL code in Database Query Strings.

Re: more complex data base queries

pmroz — Fri, 10 Nov 2023 13:15:16 GMT

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.

Re: more complex data base queries

charles_mcg — Wed, 22 May 2024 13:34:08 GMT

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