advanced SQL options

[hogi]

☐ cool new feature
☑ could help many users!

☐ removes a „bug“

☐ nice to have

☐ nobody needs it

What inspired this wish list request?
The Query Builder in Jmp is nice to generate SQL queries via a GUI.

But there are queries which cannot be generated via the GUI:

Database access 

With JMP 17, In the query builder, how can add "WITH UR" at the end of the SQL code? 

The only solution at the moment: change to custom SQL - and at that moment lose all GUI options to make further changes to the query.

What is the improvement you would like to see? 

Please improve the GUI to provide advanced SQL option:

- add the functionality to "explode" entries and "index" column entries

- allow the user to manually enter advanced SQL strings - which then can be added at specific positions of the main SQL query string.

Why is this idea important? 

Users are not forced anymore to abandon the Query Builder when they have to add an expression to their query which is not supported by the Query Builder GUI.

more wishes submitted by  

[Momentum]

I am not sure it this comments requires a separate wish item, but given the generality of the original wish item, it seems related enough to include it with this one:

I would like to see JMP incorporate an active SQL editor when linked to an external database (drop down selector as you type SQL, autofill syntax consistent commands, etc.).

Currently one can enter faulty SQL into the box and the only feedback is a general error. 

I think the original wish list item post takes it a step further and wants the Query builder GUI (or wizard as I call it) to adapt to the SQL being typed. 

[hogi]

Hi @Momentum . Thanks you for the support

An active SQL text Editor with drop down selector as you type SQL, autofill syntax consistent commands, etc 
Cool idea! I want it!

This allows me to further specify the original idea - to differentiate - and also to link ..

The original wish was born by the finding that there exist some "unknown" SQL strings  -  code which cannot be create via the GUI.

So the idea: so, either add this functionality or provide a way to add such unknown SQL strings to the auto-generated code *) - without losing the possibility to further use the GUI.

*) To do so, the GUI (wizard) doesn't have to understand the additional Strings.

 @Momentum 's active SQL editor could be a way to implement this idea - if the syntax is understood by the editor, it can support with drop boxes and error highlighting.

So, cool for users which stick to standard SQL.
And concerning *):

If there are expressions which the active SQL editor doesn't understand, it will highlight such expressions as errors - but still provide the comfortable support for the remaining strings - and for the over-all hierarchy.

The active SQL editor reminds me a lot of the Jmp Formula Editor.

So, I think this would be the ultimate solution: 

a SQL Query Editor just like Jmp Formula Editor - with in-line syntax support + hierarchy checks.

... and if the user wants to use code that is unknown to the editor - one could provide a specific functionality to do so

- some "unknown code inside" - boxes which can be placed at whatever position in the known hierarchy.

[BiasedLemur9317]

I would like to add a more specific improvement request that may also be relatively straightforward to implement: More flexibility in Computed Columns.

Currently, the formula editor only allows a limited set of functions and operators to be entered; syntax that is not recognised (including simple things such as CAST or CONVERT) is rejected. Would it be possible to either add support for the full set of functions of popular DB engines like SQL Server, or simply allow entering arbitrary SQL code to be typed in as a Computed Column formula? Maybe by adding a "Allow custom expressions" option to the drop-down menu for SQL dialects?