Edit Reports Edit SQL
Navigation: Main Menu > Setup > Edit Reports > Actions (button) > Edit SQL
Features: Code Editor
- Syntax Highlighting: Color-codes SQL keywords, comments, numbers, and strings for better readability.
- Line Numbers: Displays line numbers for easy reference.
- Tab Support: Uses a tab width of two for consistent indentation.
Buttons Explained
| Button | Description |
|---|---|
| Test | Tests the entered SQL. |
| Clear | Clears the field. |
| Save | Saves any changes. |
| Cancel | Discards the changes. |
| X (top corner) | Closes the SQL popup and generates the fields. Warning: Depending on how complex the query is, there may be a few seconds delay between closing the popup and the fields being generated. |
Tabs Explained
| Tab | Description |
|---|---|
| Tables | Lists all tables in the database (excluding system tables). Includes a context menu (right-click) with these options: – Create Select SQL: Creates the Select SQL for that table (a list of fields from which users can select the fields they need). – Show Fields: Opens a column showing all the fields that are linked to the table. Double clicking the field will add it to the note section (i.e. the section where the SQL is edited). |
| Functions | Lists user-defined, selectable PostgreSQL functions. Double click a function to insert its name. (Functions are queries that will return specific values). Includes a context menu (right-click): – Create Select SQL: Creates the Select SQL for the query. Unlike the Table, the SQL will also include the action. |
| Params | Lists query parameters (e.g., :active_user_id) and user-defined parameters from a provided list.Double-click a parameter to insert it into the query. |
Tips: Creating SQL Queries
- Parameter Handling: Parameters use a colon prefix (e.g.,
:param_name). Test queries to confirm parameters are valid. - Performance: A wait cursor appears during database operations to indicate processing.
Limitations: SQL
- The editor is designed for PostgreSQL and may not support other database systems.
- The Test feature may not catch all runtime errors.
- Navigation buttons are hidden if no grid or tree view is linked.
Troubleshooting: SQL
- Error on Test: Check SQL syntax and ensure parameters are valid. The error message provides some details.
- Empty Table/Function List: Confirm the database connection is active and the schema is accessible.
- Save Button Disabled: The query matches the saved version. Edit the query to enable the Save or Cancel buttons.
