SQL Builder: Difference between revisions
No edit summary |
|||
| (16 intermediate revisions by one other user not shown) | |||
| Line 1: | Line 1: | ||
Back to [[Documentation]] | |||
= Introduction = | |||
The SQL builder only creates SQL SELECT statements. | The SQL builder only creates SQL SELECT statements. | ||
| Line 5: | Line 8: | ||
The SQL Builder can also be accessed via the '''SQL''' button next to any nuBuilder Forte Textarea Object requiring SQL. | The SQL Builder can also be accessed via the '''SQL''' button next to any nuBuilder Forte Textarea Object requiring SQL. | ||
=Creating | =Creating a SELECT Statement= | ||
==Description== | ==Description== | ||
Description | Description for this SQL query. | ||
==Field and Relationships== | ==Field and Relationships== | ||
This is a graphical way to create table relationships. | This is a graphical way to create table relationships. | ||
| Line 15: | Line 19: | ||
Each table can be dragged by clicking on the table name. | Each table can be dragged by clicking on the table name. | ||
Each table added has.. | Each table added has... | ||
*A Tablename. | *A Tablename. | ||
*A field that will allow you to enter an alias for this table. | *A field that will allow you to enter an alias for this table. | ||
*A list of fields belonging to this table. | *A list of fields belonging to this table. | ||
*A checkbox at the left of each field name allowing that field to | *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 | *A checkbox at the top left allowing all fields to be chosen at once. | ||
| Line 27: | Line 31: | ||
#Unclick as you hover over a field on another table when its name becomes green. | #Unclick as you hover over a field on another table when its name becomes green. | ||
When hovering over an orange line you will see its type. | 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. | You can change this join type by double click it. | ||
==Clauses Subform== | ==Clauses Subform== | ||
===Type=== | ===Type=== | ||
There | There are 4 types of clauses that can be added using the SQL Builder. | ||
#WHERE | #WHERE | ||
#GROUP BY | #GROUP BY | ||
| Line 44: | Line 51: | ||
This is a [[Objects#nuScroll|scrolling]] list of available field names. | This is a [[Objects#nuScroll|scrolling]] list of available field names. | ||
===Clause=== | ===Clause=== | ||
Used by | Used by WHERE and HAVING. eg. = 1234 | ||
[[Hash_Variables]] can also be used. eg. = '#RECORD_ID#' | |||
===Order=== | ===Order=== | ||
Used by | 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. | |||
Latest revision as of 20:13, 3 February 2021
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.