SQL Builder
Back to Documentation
Introduction
The SQL builder only creates SQL SELECT statements.
These records can be used to create Tables used in Reports.
The SQL Builder can also be accessed via the SQL button next to any nuBuilder Forte Textarea Object requiring SQL.
Creating a SELECT Statement
Description
Description for this SQL query.
Field and Relationships
This is a graphical way to create table relationships.
You can add a table by making a selection from Add Table above the graphical area.
Each table can be dragged by clicking on the table name.
Each table added has...
- A Tablename.
- A field that will allow you to enter an alias for this table.
- A list of fields belonging to this table.
- A checkbox at the left of each field name allowing that field to be chosen.
- A checkbox at the top left allowing all fields to be chosen at once.
To create a relationship between 2 tables you need to do 2 things.
- Click the first field as you hover over it and its name becomes red.
- Unclick as you hover over a field on another table when its name becomes green.
This will create an orange line to represent this relationship.
When hovering over an orange line you will see its join type.
You can change this join type by double click it.
Clauses Subform
Type
There are 4 types of clauses that can be added using the SQL Builder.
- WHERE
- GROUP BY
- ORDER BY
- HAVING
Field
This is a scrolling list of available field names.
Clause
Used by WHERE and HAVING. eg. = 1234
Hash_Variables can also be used. eg. = '#RECORD_ID#'
Order
Used by GROUP BY and ORDER BY. eg. ASC or DESC
SQL
Setting SQL to Edit Manually will stop the SQL Builder overwriting the SQL statement below - if the SQL statement has been edited manually.
At the bottom of this Form is a Textarea containing the created SELECT statement.
To further edit this, double click on the field and you will open Ace Editor in a full screen.