1. Go to Query Builder > DB Connection > select a DB Interface. The list of DB objects (Tables, Views and Synonyms) of the DB connection is displayed in the DB Tree.
2. Create and edit the SQL query. You can either create the SQL query manually or edit an SQL query using the Query Builder window.
3. To execute the SQL statement, click on Execute Query.
4. Open the Results tab to view the SQL results.

Viewing the List of DB Objects

• Click the + or – icons to either expand or collapse the list in the DB objects.
• Click Refresh to refresh the DB objects list.
• Click Options and check/uncheck the Include Synonyms option to include or exclude the DB Synonyms from the DB objects list.

Editing an SQL Statement

1. Adding a DB Object to the Main Window

Select the DB object (Table, View or Synonym) from the DB Tree and proceed with one of the following actions:

• Double-click the DB object.
• Select the DB objects and drag them to the Main window.
• Select the DB objects and click on Add Selected (at the bottom of the DB Tree pane). The selected DB objects are added onto the Main window, when the related SQL statement is displayed in the Query window.

2. Removing a DB Object from the Main Window

To remove a DB object from the Main window, you can either click the DB object and press Delete on your keyboard or right-click the DB object > Remove.

Note: Such removal automatically updates the SQL query in the Query Builder window.

3. Selecting Columns in a DB Object

To select columns from a DB object, perform either one of the following actions:

• Check specific column checkboxes in the DB object to include them in the SELECT statement.
• Check the checkbox next to the asterisk in the DB object to generate the following SQL syntax:

   Select * from [Table Name]; 

• Right-click on the DB object > Check All to check all column checkboxes in the table and add them to the SQL query.

4. Removing Selected Columns from a DB Object

To remove columns from a DB object, perform either one of the following actions:

• Uncheck the checkboxes of the selected columns.
• Right-click on the DB object > Uncheck All to remove all columns of the table from the SELECT statement. Note that when this option is selected for all DB objects in the Main window, the Query Builder generates the following SQL syntax: select * from …

Advanced SQL Setting

5. Joining the Selected Tables

To add a JOIN to the SQL query, perform the following:

• Click on the DB object column (in the below example, it is the CUSTOMER_ID, in the CUSTOMER table) and drag a line to link it to a column in another DB object (CUSTOMER_ID, in the CONTRACT table). This would add an INNER JOIN to the SQL query.

• Right-click the link’s line and select one or both Select all rows from … option(s) to edit the link and update the JOIN to LEFT OUTER JOIN, RIGHT OUTER JOIN or FULL OUTER JOIN.

• Right-click on the link’s line > Properties to edit the JOIN's properties.

6. Union SQL Queries

1. Perform either one of the following actions:

• Right-click in the Main window > Union > New union sub-query/Copy union sub-query.
• Click the + next to the Q of the query (top-right corner of the Main window) to add a UNION operator, which combines the result-set of 2 or more SELECT statements.

Each SQL statement, which is combined by the UNION operator, has a Q icon in the Main window. Clicking the Q icon of each SQL statement opens it in the 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.

2. To modify the Union query, perform either one of the following actions:

• Right-click Link - - between the queries to edit the Union type.

• Right-click the Q icon to change the order of the SQL statements in the Union SQL query.

1. To remove the Union SQL query, do one of the following:\ a. Right-click the Q icon of the removed query and select Remove.\ b. Right-click the Main window, select Union > Remove.

Table of Selected Columns - Edit the SQL Statement

This table displays underneath the Query Builder's Main window, and it enables editing the SQL query. Its selected columns hold the same information as the graphical map yet in a tabular format.

Adding an SQL Function to a DB Object Column

1. Click on the 3 dots in the Expression column for opening the Expression Editor window.

2. Select the Function and the DB object column in order to add them to the SQL query.

3. Populate the Column Name column to add an alias to the selected DB object column.
4. Populate the Sort Type and Sort Order columns to add an Order by to the SQL query.
5. Populate the Aggregate column to add an aggregation function such as Min, Max, Avg to the selected DB object column. This act updates the Grouping column and is reflected as a 'Group by' statement in the SQL query.
6. Populate the Criteria and Or columns in order to add the DB object column to the WHERE statement of the SQL query.

Query Settings Toolbar

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 defines the maximum number of rows that the SQL query can return, with a default of 10,000. This limit can be adjusted as needed.