Database query designer

General information

• A database query defines the data source loaded from the database and subsequently used by edit form controls, view pages, portlets, and scripts.
• A database query determines which set of database records is loaded and passed for further processing or visualization.
• A database query is a standalone, configurable application object that encapsulates the logic of data selection and separates it from visual controls.
• The result of a database query is a dataset that is passed to controls or scripts for evaluation or display.

Standard query

• A standard query is used by all controls that directly consume data loaded from the database.
• It is the default query type in the database query designer.
• When generating the resulting SQL statement, only the clauses “SELECT”, “FROM”, “JOIN”, “WHERE”, and “ORDER BY” are used.
• A standard query does not use the “GROUP BY” clause and does not contain aggregate functions.

Aggregate query

• An aggregate query is used by browse tables and charts that display aggregated data.

• Aggregate queries work with the “GROUP BY” clause and aggregate functions.

• An aggregate query is defined in the query designer by:
  • enabling the “Aggregate query” option,
  • specifying grouping columns that appear in the resulting SQL “GROUP BY” clause.

• Supported grouping modes include:
  • grouping by column,
  • grouping by hour of day,
  • grouping by hour,
  • grouping by day,
  • grouping by week,
  • grouping by month,
  • grouping by quarter,
  • grouping by year.

• An aggregate query further defines:
  • aggregated columns using the functions “COUNT”, “SUM”, “AVG”, “MAX”, and “MIN”, optionally with conditional logic via “CASE WHEN”,
  • informational columns represented as constants or calculated values evaluated by the lexical analyzer,
  • columns available for filtering in view tables or charts,
  • header definitions for grouping columns in view tables,
  • color definitions used for rendering chart series.

• Aggregate query columns use the following naming syntax:

“name@math_expression@display_value@condition”.

• Name defines the column label displayed in the view table or chart.
• Math expression is optional and applies to “Expression, format by” column types.
• Display value is optional and allows replacing calculated values with text or hiding the column using “@@”.
• Condition is optional and restricts which result rows are displayed.

• Example column names:
  • “Sum of first and second column@#c0#+#c1#”,
  • “Sum of first and second column@#c0#+#c1#@Hidden value”,
  • “Sum with value on next row@+1”,
  • “Sum with value on previous row@-1”,
  • “Sum of first and second column and value on next row@+1#c0# + #c1#”,
  • “Sum of values greater than 5@@@>5”,
  • “Column@+sum”,
  • “Column@-sum”.

• Header definitions use the syntax:

“merged_cell_count@header_name”.

• Header examples:
  • Row 1: “5@X;1@Y”,
  • Row 2: “2@A;3@B;1@C”.

Nested records

• A nested record is a database record linked to its parent record using the foreign keys “pid” and “pform”.
• These foreign keys are present in every database table and define the parent record ID and the parent edit form ID.
• References using “pid” and “pform” may point to records in another table or within the same table (recursive structures, e.g. tree controls).
• The values of “pid” and “pform” are filled automatically based on the context from which a new record is created.
  • When created from a view page, both values are set to “0”.
  • When created from an existing database record, they are set according to the parent record and its form.
• The “pform” column is used only in special cases, typically when one table serves multiple parent forms.
• The “pid” and “pform” columns are not indexed by default. When actively used, indexing must be enabled in the edit form settings.
• View tables in edit forms provide the option “Show nested records only”, which automatically adds the condition “PID = current record ID”.
• View tables on view pages provide the option “Hide nested records”, which automatically adds the condition “PID = 0”.

Query filtering conditions

• A database query may contain any number of filtering conditions following the “WHERE” clause.

• Individual conditions can be combined using the logical operators “AND” and “OR” and grouped using parentheses.

• The final SQL condition is generated automatically based on the list of conditions defined in the graphical query designer.

• The following extended condition syntaxes are supported and automatically translated into the corresponding SQL expressions:

• For the data types “Integer” and “Long”, a condition written as “left_side = 1;2;3” is interpreted as “left_side IN (1, 2, 3)”.
• For the data type “String”, a condition written as “left_side = (array)A;B;C” is interpreted as “left_side IN ('A', 'B', 'C')”.

• If a control of type “MultiListBox” (values separated by tab characters) is used on the left-hand side of the condition and a text constant is used on the right-hand side, the condition is interpreted as:
  • “JoinText2(left_side, right_side)” for Firebird databases,
  • “dbo.JoinNtext(left_side, right_side)” for Microsoft SQL Server databases.

The resulting SQL query evaluates all records whose MultiListBox column contains at least one value equal to the text constant on the right-hand side.

• If a “MultiListBox” control is used on both the left-hand and right-hand sides of the condition, the condition is interpreted using the same functions (“JoinText2” for Firebird or “dbo.JoinNtext” for MSSQL). The resulting SQL query evaluates all records for which at least one matching value exists in both columns.

• For the data type “String”, a condition written as “left_side = (mlb)A#tab#B#tab#C” is interpreted as:
  • “JoinText2(left_side, 'A B C')” for Firebird databases,
  • “dbo.JoinNtext(left_side, 'A B C')” for Microsoft SQL Server databases.

The resulting SQL query evaluates all records whose tab-separated value column contains at least one value that is part of the text constant on the right-hand side.

• A query condition may also be defined directly using SQL syntax.
  • In this case, the left-hand side of the condition, including the operator, may be arbitrary.
  • The right-hand side of the condition must always begin with the prefix “OK#crlf#”, followed by the SQL condition itself.

• Examples of direct SQL condition definitions:
  • OK#crlf#0=0
  • OK#crlf#0=1
  • OK#crlf#EQUALS(ng_tb, "", 0=0, ng_tb = FORMATSTRINGSQL(#ng_tb#))
  • OK#crlf#EQUALS(ng_tb, "", 0=1, ng_tb = FORMATSTRINGSQL(#ng_tb#))

Joins

• A database query may contain any number of table joins defined using the “JOIN” clause.

• The resulting SQL join expressions are generated automatically based on the list of joins configured in the graphical query designer.

• In standard scenarios, joins are configured exclusively through the designer and authors do not work directly with SQL join syntax.

• Joins can also be defined manually using SQL syntax.
  • In this case, the left-hand side of the join definition may be arbitrary.
  • The right-hand side of the join definition must always begin with the prefix “OK#crlf#”, followed by the actual SQL join definition.

• Example of a manual join definition:
  • OK#crlf#INNER JOIN ng_abc J1 ON J1.pid = ng_formular.id

• When defining joins manually, the following rules must be respected:
  • Join aliases must follow the join order – the first join uses alias “J1”, the second “J2”, and so on.
  • The join definition must respect the name of the source database table used by the query.

• Manual join definitions represent an exception to the standard configuration workflow and are intended only for advanced use cases where the graphical designer is not sufficient.

1. List of tabs in the settings dialog database query

• General – Setting general properties
• Conditions – Definition of restrictive conditions
• Joins – Definition of joins
• Columns – Aggregate table column definitions
• Headers – Aggregate table header definitions
• Colors – Graph color definition
• Other – Setting other properties

1.1. “General” tab

1.1.1. Database table

• Select the edit form from whose database table the records stored in the database will be retrieved.

1.1.2. Options

• Aggregate query - Checking this box determines whether the query should result in an aggregated data set compiled using grouping.

1.1.3. Sort by

• Selection of the column according to which the database records will be sorted, including the sorting method – ascending (ASC) or descending (DESC).
• Optional selection of the second column according to which the database records will be sorted, including the sorting method – ascending (ASC) or descending (DESC).

1.1.4. Color by

• A column selection that determines whether and by which column a colored rectangle will appear in each view table.

1.1.5. Time span by

• Column selection, which determines whether and according to which column a filter for selecting the “from-to” time period will be displayed above the view table.

1.2. “Conditions” tab

• Definitions of query constraints that follow the “WHERE” clause of a database query.

1.2.1. Add condition

• You can use the “Add condition” button to add a new query condition.

1.3. “Joins” tab

• Definitions of joins that are built using the “JOIN” clause of a database query.

1.3.1. Add join

• You can use the “Add join” button to add a new query join.

1.3.2. Refresh

• Using the “Refresh” button, the list of columns on the left and right side of the condition is updated based on the selected accepted table.

1.4. “Columns” tab

• Only when the “Aggregate query” box is checked
• Definition of the columns of the resulting aggregation table.

1.4.1. Add column

• Using the “Add column” button, it is possible to add a new column to the resulting aggregation table.

1.5. “Headers” tab

• Only when the “Aggregate query” box for the “DataGrid” or “LiteDataGrid” control is checked
• Definition of the headers of the resulting aggregation table.

1.5.1. Add header

• Using the “Add header” button, it is possible to add a new header to the resulting aggregation table.

1.5.2. ?

• Use the “?” button to display header syntax help.

1.6. “Colors” tab

• Chart control only
• Definition of colors that will be used to draw individual columns of the chart.

1.6.1. Add color

• Use the “Add color” button to add a new chart color.

1.7. “Other” tab

1.7.1. Template name

• The template name is used to name the database query with the option to copy it when creating other database queries with the same source database table.
• When creating a new database query, all available templates are available in the “Templates” drop-down list on the “General” tab. After selecting a template, all parameters of the database query will be automatically pre-filled with data from the selected template.
• A list of all database queries that are marked as templates can be displayed using a report. A detailed description of the reports is given in a separate manual “Reports”.

1.7.2. Notes

• Notes are used to enter any text intended for the application administrator.

1.7.3. Load only first

• Limitation of the maximum number of records retrieved by a database query resp. the SQL equivalent of the TOP() or FIRST() statement.

1.7.4. Remove records with duplicate

• Selects the column according to which duplicate rows in the retrieved data set will be evaluated, and these rows will then be removed from this set.

1.7.5. Options

• ngef(NETGenium.DataTable)
  • The result of a database query is always a set of data from the database, temporarily stored in an object of type “DataTable”. This data set is then passed to the individual controls for evaluation or visualization.
  • Checking this box determines whether an external function should be run before passing the “DataTable” object to the control, which has the option to change the properties of this object – add rows, change values in individual columns, or delete rows.

1.7.6. Logging

• Using the “Logging” button, a detailed report is displayed with individual records of database query calls and data about
  • the date and time the query was started,
  • the user who initiated the query
  • query processing time in milliseconds,
  • the number of records returned, and
  • specific SQL query.

• The number of records is limited to 100 by default. This number can be manually increased or decreased by changing the “maxrows” parameter in the report URL.