To use the Query Builder:
Note: The Insert, Update and Delete statements (performed by the DB query) may cause an auto-commit to the DB (based on the DB driver’s definition).
Expand or collapse the DB objects tree. When opened via the DB Interface Explorer, the DB objects tree looks as shown in the below image.
A similar tree appears inside the Query Builder pop-up window, when the Query Builder is opened via Schema Editor, Graphit and Broadway. Select the relevant Interface and then expand or collapse the DB objects tree, as needed.
Select the DB object (Table, View or Synonym) from the DB Tree and do either:
Either click the DB object and press Delete on your keyboard or right-click the DB object > Remove. Note: The removal of a DB object automatically updates the SQL query in the Query Builder window.
To select columns from a DB object, do either:
Select * from [Table Name];
To remove columns from a DB object, do either:
To add a JOIN to the SQL query, do the following:
Each SQL statement that is combined by the UNION operator has a Q icon in the Main Window. Clicking the Q icon of each SQL statement opens its Main window, where you can add DB objects and link them to each other in order to edit the SQL statement in the Union query.
This table is displayed at the bottom-right of the Main window and it enables editing the SQL query. Its selected columns hold the same information as the graphic map in a tabular format.
The Enable Pre-Execution Commands checkbox enables running commands on the selected DB before running the SQL query. When this checkbox is checked, the Pre-Execution Commands window opens. Note that when running the Query Builder on a Fabric interface, you should run the [Get Instance] command in the Pre-Execution Commands window as the SQL query must run on an LU Instance level.
The Max rows setting is used for setting the maximum number of rows returned by the SQL query. The default value is 10,000 rows. It is possible to set a different number of rows to be returned by the SQL query.
You can either create the SQL query manually or use the DB objects tree menu.
Adding a query to the Query Editor panel using the DB Object Tree, is depended on its occurrence - at DB Interface Explorer, or when opened as popup at Schema Editor, Graphit and Broadway.
At DB Interface Explorer, choose the required SQL statement by using the context menu (right click) on the relevant tree entry. The available statements are: select statement, insert statement, update statement, delete statement.
You can choose either a table or specific table's fields so that the query will contain only those fields.
At Query Builder popup, you can click on the right arrow, which appear on hoovering a table in the tree. Once clicked, a "select *" statement from that table is added to the Query Editor editing board.
While editing the SQL statements manually, you can be assisted by the Editor Assistant, which suggest you code completion. The completion suggestion is for schemas, tables, columns names of the current interface, as well as set of base SQL clauses.
The Assistant code completion is done while typing and on hitting CTRL+SPACE, as used while code programming.
Notes
- You can clear the whole Query Editor window by clicking on the Clear button.
- The Query Editor window can hold and execute several queries. When clicking on the Execute button, all queries will be executed, one by one, where the Results window will show the results of the last query.
- If the Query Editor window contains several queries and you wish to run some of them, there is no need to delete the others. To execute specific commands, select them and then click on Execute.
- You can add Fabric commands to the Query Editor and they will also be executed.
- When Fabric is the selected data source interface, set the top bar fields before executing the query.
To use the Query Builder:
Note: The Insert, Update and Delete statements (performed by the DB query) may cause an auto-commit to the DB (based on the DB driver’s definition).
Expand or collapse the DB objects tree. When opened via the DB Interface Explorer, the DB objects tree looks as shown in the below image.
A similar tree appears inside the Query Builder pop-up window, when the Query Builder is opened via Schema Editor, Graphit and Broadway. Select the relevant Interface and then expand or collapse the DB objects tree, as needed.
Select the DB object (Table, View or Synonym) from the DB Tree and do either:
Either click the DB object and press Delete on your keyboard or right-click the DB object > Remove. Note: The removal of a DB object automatically updates the SQL query in the Query Builder window.
To select columns from a DB object, do either:
Select * from [Table Name];
To remove columns from a DB object, do either:
To add a JOIN to the SQL query, do the following:
Each SQL statement that is combined by the UNION operator has a Q icon in the Main Window. Clicking the Q icon of each SQL statement opens its Main window, where you can add DB objects and link them to each other in order to edit the SQL statement in the Union query.
This table is displayed at the bottom-right of the Main window and it enables editing the SQL query. Its selected columns hold the same information as the graphic map in a tabular format.
The Enable Pre-Execution Commands checkbox enables running commands on the selected DB before running the SQL query. When this checkbox is checked, the Pre-Execution Commands window opens. Note that when running the Query Builder on a Fabric interface, you should run the [Get Instance] command in the Pre-Execution Commands window as the SQL query must run on an LU Instance level.
The Max rows setting is used for setting the maximum number of rows returned by the SQL query. The default value is 10,000 rows. It is possible to set a different number of rows to be returned by the SQL query.
You can either create the SQL query manually or use the DB objects tree menu.
Adding a query to the Query Editor panel using the DB Object Tree, is depended on its occurrence - at DB Interface Explorer, or when opened as popup at Schema Editor, Graphit and Broadway.
At DB Interface Explorer, choose the required SQL statement by using the context menu (right click) on the relevant tree entry. The available statements are: select statement, insert statement, update statement, delete statement.
You can choose either a table or specific table's fields so that the query will contain only those fields.
At Query Builder popup, you can click on the right arrow, which appear on hoovering a table in the tree. Once clicked, a "select *" statement from that table is added to the Query Editor editing board.
While editing the SQL statements manually, you can be assisted by the Editor Assistant, which suggest you code completion. The completion suggestion is for schemas, tables, columns names of the current interface, as well as set of base SQL clauses.
The Assistant code completion is done while typing and on hitting CTRL+SPACE, as used while code programming.
Notes
- You can clear the whole Query Editor window by clicking on the Clear button.
- The Query Editor window can hold and execute several queries. When clicking on the Execute button, all queries will be executed, one by one, where the Results window will show the results of the last query.
- If the Query Editor window contains several queries and you wish to run some of them, there is no need to delete the others. To execute specific commands, select them and then click on Execute.
- You can add Fabric commands to the Query Editor and they will also be executed.
- When Fabric is the selected data source interface, set the top bar fields before executing the query.