Web System Common Tools

Web System Applications Development

 Web System Database Web System PostgreSQL Tables Web System Manage fields Web System Manage constraints Web System Manage indexes Web System Manage triggers Web System SQL Tab Web System Single-row editing Web System Multirow editing in table mode Web System Import Web System Export Web System Table transfer - or Store Web System Composite Types Web System SQL Queries Web System Workflow Web System Dataset Design Web System Query Expressions Web System Where Conditions Web System Group by Expressions Web System Having Condition Web System Windows Clause Web System Order by Expressions Web System SQL Tab Web System Test a Query Web System Views Web System Functions Web System Datasources Web System Charts Web System Main type selection Web System Subtype selection Web System Datasource selection Web System General settings Web System Axis settings Web System Background settings Web System Margin settings Web System Color settings Web System Mark settings Web System QR Barcodes Web System Saving Web System Reports Web System Template Editor GYRE Web System Sections Web System Toolbar Web System Basic properties Web System Data formatting Web System Text Web System Data Web System Macro Web System Geometry Web System Line Web System Bezier curve Web System Image Web System Chart Web System Arrangement Web System Clipboard Web System History Web System Zoom Web System Import/export Web System Delete Web System Report Generator Web System Report Schedule Web Site Builder Web System Content Management System (CMS) Web System Edit site Web System Themes and pages Web System Create and edit theme Web System Edit page Web System Web Form Builder / Content Editor (GYRE) Web System Toolbar Web System Events Web System Basic properties Web System Text field Web System Button Web System List Web System Combobox Web System Label Web System Checkbox Web System Radiobutton Web System Filler Web System Upload file Web System Additional tools (Gears) Web System Image Web System Flash video Web System Arrangement Web System Clipboard Web System History Web System Zoom Web System Import/export Web System Delete Web System Operations with objects Web System DNS Manager Web System SEO tools Web System App Analyzer Web System Reindex Web System Publish Web System Access Log Web System Checkout settings Web System PHP Scripts development (Actions) Web System Source Editor Web System API functions Web System Custom Scripts development (JS, CSS, XML, etc...) Web System Gears Web System SaaS Solution Manager

Web System Cook book

Web System Deprecated

Web System Workflow

To create a query, select Query Builder in the Tools menu or click New Query in the section Queries. 

First step is selecting the type of query. There are four query types:

  • SELECT – selects rows in a table;
  • INSERT – adds rows to a table;
  • UPDATE – changes table rows;
  • DELETE – deletes rows from a table;
  • Edit SQL - opens SQL tab for manual query text entry (for experienced users).

Query type selection.

Selecting query type opens Dataset Design tab that visually layouts SQL query. On the left there is a list of tables (Table), views (View), and functions (Function). Double-clicking the name in the list automatically adds it to the visual layout area. This area is intended for setting up joins between the data sets in the query.

A join combines records from two or more tables in a database and creates a set that can be saved as a table or used as is. A join is a means for combining fields from two tables using values common to each.

Join predicate identifies the records for joining. If the evaluated predicate is true, the combined record is then produced in the expected format, a record set or a temporary table, for example.

To create a join, select a field in the first set of data and holding the left mouse button drag it to the desired field in the second set of data. A line will be drawn from the first set of data to the second, creating a join.


Query Builder with open Dataset design tab.

To change the type of join click on the green diamond in the middle of a join line, displaying a drop-down menu with four possible options :

  • Left join
  • Right join
  •  Inner join
  • Full join

Inner join creates a new result table by combining column values of two tables based upon the join predicate. The query compares each row of the tables to find all pairs of rows which satisfy the join predicate. When the join predicate is satisfied, column values for each matched pair of rows are combined into a result row.

Outer joins do not require each record in the two joined tables to have a matching record. The joined table retains each record — even if no other matching record exists. Outer joins subdivide further into left joins, right joins, and full joins, depending on which table(s) retains the rows from (left, right, or both).

Left join always contains all records of the "left" table, even if the join condition does not find any matching record in the "right" table. This means that it returns all the values from the left table, plus matched values from the right table (or NULL in case of no matching join predicate). Right join closely resembles the left join, except with the treatment of the tables reversed.

Full join combines the results of both "left" and "right" tables. The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

Editing join type.

When data sets are added and joins are established, click on the tab Expressions to select fields and /or expressions for the columns in the result dataset.

Tab Expressions.

Click Add new to add a field or expression to a query. This displays Add Expression dialog where you can enter the field name and expression.

Add Expression dialog.

All of the available functions are listed on the left under Functions, all fields of datasets are listed in the center, under Fields. Block of operators is on the right. It should be noted that the vertical selection may contain duplicate rows. If you want to get a unique row, you can use the keyword DISTINCT. To do this, set the flag Select Distinct records.

The next optional step lets you select a condition for a query (operator WHERE). To add conditions to a sample click on the tab Where Conditions, then click Add new. This displays the standard fields and expression selection dialog. The WHERE clause eliminates all rows from the result set for which the predicate is not evaluated true.

Where Conditions Tab.

Next tab GROUP BY (an optional step) is used to define result-sets to which aggregate functions (COUNT, MIN, MAX, AVG and SUM) may apply. Aggregate functions return a single value, calculated from values in a column.The GROUP BY expression is used in conjunction with the aggregate functions to group the result-set by one or more columns. When a column of data is grouped, NULL values count just like any other value and put in one group for the purposes of aggregate functions. Used without aggregate functions, GROUP BY acts like DISTINCT. It divides the table into groups and returns one row for each group.

Tab HAVING specifies that the result-set will only include rows where aggregate values meet the specified conditions. It is similar to WHERE Condition, but WHERE cannot work with aggregate functions.

WINDOW Clause. WINDOW  is a user defined set of rows. A window function computes a value for each row in a result set derived from the window.  Find out more about window functions here

WINDOW dialog.


To sort the rows of a result-set use the tab ORDER BY Expression and click Add New. ORDER BY identifies which columns are used to sort the resulting data, without  an ORDER BY Expression the order of rows returned by a query is undefined.

Order by expression tab.

You can manually edit a query in SQL Tab. Click Apply to finalize changes.  Error message will pop-up if Query Builder finds a mistake.

Click Refresh in Test query tab. It will run the query and result will be displayed in a table format. To edit query click Parameters.

Query result set.