When using the GUI for SQL queries (which is super helpful for speed of development and understanding), something I’m running into is using resources that have permissions to select, update etc to a particular table, but do not have them within the referenced schema
This is not an issue for referencing the table itself, because you can reference the table using the function mode (FX), but then the columns cannot be referenced since they do not appear in the dropdowns, but I would be able (and happy to) fill these out manually instead of using dropdowns if possible.
Correct - the GUI option tries to provide tables and columns in a dropdown field by default
The table can be changed to a ‘formula’ field which allows me to select tables that I know the resource can access, but are not on the list of tables provided in the dropdown
But then for any GUI query that requires selecting a column (insert values, update values etc), I have empty dropdowns instead of having values.
Obviously I can just complete the same tasks in pure SQL (which is what I’ve ended up doing), but if we can select the table manually, it would be great to just input the column names manually as well
Not a major issue, but just flagging since it seems like a low lift to simply allow for manual input in a particular field
Can you share a screenshot of where in a GUI query you were wanting the columns to appear in a dropdown? Or a screen recording could be very helpful as well.
Are you referring to the options under "Key value pairs"?
It is unfortunately a known limitation for dynamic columns that they do not play nicely with the GUI, I can make a feature request for this once I get more understanding
Glad to hear that you found a workaround with using pure SQL for now!
When using the GUI - it is possible to use an inputted value for the database.schema.table name, but then it is impossible to use key value pairs as an input method since there is no option to input the column names, they can only be provided via dropdown which is not populated
The ask is effectively to allow for the same dynamic input options for the keys as is available for the table
I see what you are saying, table names can be assigned dynamically via FX but keys in the changeSet must be selected from the dropdown.
I can definitely make a feature request for this.
On a side note, the drop down should be populating with column names once a query runs in the editor.
One idea for a work around in the short term would be to have several queries, one for each row/action you want to take, and have some in app logic determine which query is used.
