CS

Database query designer

Home / Documentation / Administrator guide /

Database query designer

CS

1. 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.

1.1. 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.

1.2. Aggregate query

  • An aggregate query is used by view 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.
  • The query is built as an aggregate query only if it contains at least one aggregated, informational or expression column; with grouping columns alone it is loaded as a standard query.
  • Supported grouping modes include:
    • grouping by column,
    • grouping by hour of day,
    • grouping by hour,
    • grouping by hour and minute,
    • 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. The condition filters entire result rows (for example, “>5” keeps only the rows in which the column value is greater than 5), and the totals in the footer are calculated from the displayed rows only.
  • Example column names:
    • “Sum of first and second column@#c0#+#c1#”,
    • “Sum of first and second column@#c0#+#c1#@Hidden value”,
    • “Running total from the top@+1”,
    • “Running total from the bottom@-1”,
    • “Running total of first and second column@+1#c0# + #c1#”,
    • “Sum of values greater than 5@@@>5”,
    • “Column@+sum”,
    • “Column@-sum”.
  • Special expression values:
    • “+1” at the beginning of the expression – running total from the top: the value of each row is added to the total of all previous rows; the running total starts again when the value of the grouping column changes,
    • “-1” at the beginning of the expression – running total from the bottom,
    • “++1” and “--1” at the beginning of the expression – a running total that also includes the values from the period before the start of the time span,
    • “+sum” at the end of the expression – the column value is added to the totals row,
    • “-sum” at the end of the expression – the totals row stays empty for the column.
  • The variables “#c0#”, “#c1#” etc. in an expression refer to the result columns in this order: grouping columns, the column grouped by date, and then the other columns. An expression can use the value of another expression column only if that column is listed earlier; an error in the expression results in the value 0.
  • 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”.

1.3. 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”. The “pform” value is not tested, and for a new record that has not been saved yet, the table displays nothing.
  • View tables on view pages provide the option “Hide nested records”, which automatically adds the condition “PID = 0”.

1.4. 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.
  • A condition testing whether a value is (not) filled in must, for columns of the “String” data type, take both forms of an empty value into account – a zero-length text string as well as the database value “null”. The “is not defined” operator alone catches only the “null” value – it does not catch an empty string.
    • Such a condition is composed: left_side equals “” or left_side is not defined. In SQL: left_side = '' OR left_side IS NULL.
    • For controls of type “ForeignKey”, “File”, and “Image”, analogously: left_side equals “0” or left_side is not defined. In SQL: left_side = 0 OR left_side IS NULL.
    • A detailed description of testing for an empty value is provided in the separate guide Working with variables in scripts.
  • The operators “does not equal”, “does not contain”, “does not begin with” and “does not end with” do not return records that have the value “null” in the column. The condition “is not in query” returns nothing if the nested query returns the value “null”.
  • If the value of a condition is a variable and the variable is empty, the condition is not omitted; it compares the column with an empty value – an empty string for text, zero for a number; a condition on a date returns nothing in such a case.
  • The value of a condition can contain special values:
    • “#random#” – a random number from 1 to the highest record ID in the table, typically in the condition “ID = #random#”; the number does not have to match an existing record,
    • “#dayrandom#” – the same, but the number changes once a day and is the same for all users,
    • “#weekend#” – selects records whose date falls on a Saturday or Sunday.
  • 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:
    • “JoinText(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 (“JoinText” 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.
    • A direct SQL condition is created only when the administrator writes the prefix “OK#crlf#” directly in the condition value, or when the condition is returned by a function written in the condition value (for example “ngef(...)” or “EQUALS(...)”). A value substituted from a variable (for example “#ng_note#” or “#A#”) is always inserted as a literal, even if it starts with the text “OK” and a line break.
    • Variables used inside a direct SQL condition are not escaped – a value that a user can fill in must be wrapped in the “FORMATSTRINGSQL”, “FORMATINTSQL” or “FORMATDATESQL” function.
    • A direct SQL condition is not used in a user's composed filter.
  • 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#))

1.5. 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.
    • As with a condition, a direct SQL join is created only from a value written by the administrator or returned by a function; a value substituted from a variable is always inserted as a literal.
  • 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.

2. 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

2.1. “General” tab

2.1.1. Database table

  • Select the edit form from whose database table the records stored in the database will be retrieved.
  • Changing the database table of a saved query creates a new database query; the original one remains unchanged in the database.
  • For controls that load a value from the query (for example “ComboBox”, “ForeignKey” or a script step), the “Column” field is displayed below the database table – the selection of the column whose values the query returns.

2.1.2. Options

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

2.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).

2.1.4. Color by

  • A column selection that determines whether and by which column a colored rectangle will appear in each view table.
  • The “Color text” option additionally colors the text of the record with the record color; the colored rectangle remains displayed.
  • For the “ForeignKey” control, the field is called “The value saved in the database” and determines the column whose value is saved in the foreign key instead of the record ID. When this field is changed for an existing query, the dialog offers a conversion of the already saved values, which overwrites the foreign key values in the whole table and in its change history.

2.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.
  • The time span applies to controls with the “from-to” filter displayed. In a portlet, in the “LiteDataGrid” control and in a script it does not apply at all – the query loads records there without any time span restriction.

2.1.6. From

  • The default value of the beginning of the time span, which is pre-filled in the “from-to” filter above the view table.
  • If the field is not filled in, the default value “one month ago” is used, i.e. today's date minus one month. The time span is therefore limited even if the field is left empty – an empty value does not mean that data retrieval is unlimited.
  • The value can also be entered using variables, for example “#today#”. Variables are evaluated when the control opens up.

2.1.7. To

  • The default value of the end of the time span, which is pre-filled in the “from-to” filter above the view table.
  • If the field is not filled in, the default value “the last day of the year” is used, i.e. December 31 of the current year. The time span is therefore limited even if the field is left empty – an empty value does not mean that data retrieval is unlimited.
  • The value can also be entered using variables, for example “#today#”. Variables are evaluated when the control opens up.

2.1.8. Options

  • The “from” and “to” values last selected by the user in the filter are recorded separately for each user and each database query. The following two fields determine whether these saved values are restored the next time the control opens up, or whether the default value is used.
  • Fill in the default value “From” every time the control opens up - Checking this box determines that the last selected “from” value is not restored and that the default value is used every time the control opens up. By default, this box is not checked, so the last selected value is restored.
  • Fill in the default value “To” only the first time the control opens up - Checking this box determines that the last selected “to” value is restored and that the default value is used only the first time the control opens up. By default, this box is not checked, so the default value is used every time the control opens up.
  • Saved values are restored only on a view page, not in an edit form, and they are not restored for the anonymous user.
  • Changing the default “From” value in the designer clears the last selected “from” value for all users; changing the sorting in the designer overwrites the saved sorting for all users.

2.1.9. Show query

  • “Show query” button – Displays the part of the database query built from the unsaved values of the dialog: the database table, joins and conditions (“SELECT * FROM ... JOIN ... WHERE ...”). It does not contain the selected columns, sorting, the record limit or the time span.

2.2. “Conditions” tab

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

2.2.1. Add condition

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

2.3. “Joins” tab

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

2.3.1. Add join

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

2.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 joined table.

2.4. “Columns” tab

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

2.4.1. Add column

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

2.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.

2.5.1. Add header

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

2.5.2. ?

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

2.6. “Colors” tab

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

2.6.1. Add color

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

2.7. “Other” tab

2.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 template drop-down list next to the dialog buttons. After selecting a template, the parameters of the database query are pre-filled with data from the selected template, including the source database table; the record limit, the external function and the notes are not taken over. Nested queries of the template are copied to the database as soon as the template is selected.
  • A list of all database queries that are marked as templates can be displayed using a report. A detailed description of the reports is provided in the separate guide Reports.

2.7.2. Notes

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

2.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.
  • For an aggregate query it does not apply in SQL – it limits only the number of displayed or exported rows.
  • When duplicate records are removed at the same time, duplicates are removed only from the loaded rows, so the result may contain fewer records than the set number.

2.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.
  • Duplicate rows are removed after the data is loaded from the database; the first occurrence in the current sort order is kept and the comparison is case-sensitive. Rows with the value “null” are not removed. The field is not displayed for an aggregate query.

2.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.
    • In NET Genium Online this field is not displayed – external functions are not available in the cloud.

2.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.

2.7.7. Change tracking

  • “Change tracking” button – View a detailed report with the change history of the database query definition: who saved the query in the designer and when, and what the query looked like at that moment. Changes made through the MCP server or by an application import are not written to the history.

2.8. “Save”, “Delete” and “Close” buttons

  • “Save” button – Save changes to database query settings. The change takes effect immediately, even if the settings of the parent control are closed without saving. The button is inactive if the administrator is not allowed to change the application the query belongs to (restriction of the admin mode of the application group).
  • “Delete” button – Unlink the database query from the control. The query record remains in the database.
  • “Close” button – Close the database query settings dialog without saving changes.