SQL folder

SQL folders are recommended when you want to select certain objects from a large amount of data based on a simple condition. Unlike the expression folder, the selection of data is done by the database server, which in most cases is faster. The disadvantage of SQL folders is that they do not automatically respond to changes in data and that the flexibility of queries is limited compared to expression folders (with OCL).

An example of an SQL folder is the folder Adressen in the root data of Vertec. If you select this folder, you will see the search dialog above the list, in which you can specify certain criteria. Only the entries that match these criteria will appear in the folder.

The query for an SQL folder consists of a designation and an SQL condition. The SQL condition must be a valid SQL expression that returns a Boolean value. For evaluation, the SQL condition is used as the WHERE condition in an SQL-SELECT command. The condition (whereAttributes) can have a maximum length of 8000 symbols.

The Properties dialog of an SQL folder allows dialog-based editing of the SQL queries. Right-click on the folder in which you want to create the SQL folder and select Neu > SQL-Ordner.

In the SQL Folder dialog, click Abfragen definieren.... The following window appears:

For simple queries, you can now enter the appropriate SQL expression in the SQL field. A normal text field appears to enter the value.

Most database fields are identical to the attribute names used in OCL, but there are differences in the case of calculated attributes and links. For a collection of SQL folder examples, see SQL Expressions for SQL Folders.

If you want to use several fields at the same time for a search or if you need a field of another type (e.g. a date field), you can define search fields. A maximum of 9 fields are allowed per query.

Using the button $vtc__000__888_0right under Fields a new search field is inserted and named. A search field is structured as follows:

designation	The search field is shown in the query dialog with this designation. It should show what you are looking for.
Field type	The following types of search fields are supported: Symbol: Normal text search field. Number: Search field for a numeric value. Checks if it is a valid numeric value. Date: Date search field with date picker for date selection. True/False: Checkbox for a Boolean query. Selection: Displays a drop-down list from which a value can be selected. Once this type is set, a field for the expression appears just below it. The expression must return a list of strings as a result. A selection field is then shown on the interface from which the desired value can be selected. By default, the entry specified under Default value is shown, if one is defined. If a default value is defined, it is shown by default. If the specified default value does not exist in the drop-down list or it does not have a default value, the first entry of the drop-down list is taken.
Default value	Here you can enter a default value using an OCL expression. This is already in the field when the user invokes the query, but can be changed. When you open the OCL expression editor, the base class is always Projektbearbeiter, even if other objects are queried. This is the logged-in user. In order to use the logged-in user as the default value, the expression self.asstring can be entered here. If the search field is of the Date field type, a drop-down list will also appear from which you can select one of the following default values: No date Today’s date First of the month Last of the month First of last month Last of the last month If the search field is Boolean (True/False), enter the default value as follows: Default value TRUE: Enter value 1 as a number. Default FALSE: Enter nothing.

On the interface, the fields then appear in the query for selection:

From Vertec 6.2 onwards, the folder search can also be executed directly by activating the Sofort Suche in the query dialog. This allows the search to be executed directly when the folder is displayed with the default search parameters (e.g. the logged-in user) without having to click on Suchen.

Further searches can be made as usual by changing the search parameters and using the search button.

For the search dialog to appear on the interface, the option Suchdialog anzeigen must be clicked in the SQL folder Configuration. If no search dialog is displayed, the first query is always used to calculate the folder contents.

If this option is enabled, it will have a Show All button next to the Search button. Clicking on this button will show all entries, regardless of queries or criteria.

For projects and addresses, a checkbox Only active is shown by default.

Whether the check mark is set by default or not can be controlled with the system setting General > Consider deactivated entries in search dialogs.

As of Vertec 6.3, this option can also be hidden. In the query definition dialog there is an option 'Nur aktive' ausblenden:

This has the following effects:

• The Only active checkbox is no longer shown on the interface.
• If the checkbox is hidden, the search always takes place in all entries, i.e. in active and inactive. If the option is hidden, the above-mentioned system setting Take account of deactivated entries in search dialogs is ignored.

You can also define multiple queries per folder, even those with multiple fields if required. To create a new query, click the + on the left of the queries.

If the folder is configured in this way, the different queries are available as tabs above the search dialog:

The fields are queried in order per query, from top to bottom. The variables are \1 for the first field, \2 for the second field, \3 for the third field, etc.

For example, a search by date range would look like this:

datum >= '\1' and datum <= '\2'

You can also define this query so that only a start date or an end date can be entered and the query still works. In this case, it must read:

(('\1'='') or (CreationDateTime>'\1')) and (('\2'='') or (CreationDateTime<'\2'))

If you extend a query with a Boolean query, note the following:

Suppose a query queries all invoices. What happens if we add a True/False query to query whether to include open or settled invoices?

As an example, use a query for date and number. This is:

datum < '\1' AND (nummer like '\2%')

Since the query now has to be expanded by the True/False field, this means that the meaning of the original query changes. For example, if you add a checkbox “nur verrechnete” to the invoice query, the previous query has to be expanded as follows:

... AND verrechnet=\3

This means that if nothing is specified, it will now only search for open ones!

To work around this, you can add a CASE WHEN to the query:

AND verrechnet=(CASE WHEN \3=1 THEN 1 ELSE verrechnet END)

If parameter 3 (our checkbox) is equal to 1 (i.e. it is checked), then we compare the charged field with 1 (only the charged ones), if parameter 2 is equal to 0 (i.e. unchecked), we compare charged with charged, i.e. all are found.

In this case, the whole query is:

datum < '\1' AND (nummer like '\2%') AND verrechnet=(CASE WHEN \3=1 THEN 1 ELSE verrechnet END)

Important information:

• Checkboxes always have a value. They are either True or False. For text fields, a message will appear if nothing is entered. If a checkbox is defined as a search field, then such a message will not appear, even if there is nothing in it in all text fields, because an inactive checkbox has the value False and the search is started with it.
• When querying addresses (class address entry) and projects (class project), you should not use a checkbox to query the Aktiv status, as these classes have a checkbox Also deactivated, which already implements this (see section Also deactivated, above).

Starting with version 6.1 it is possible in the new apps to access the entered search parameters in the column expression. This makes it possible, for example, to use date ranges used for the SQL query also in the OCL expressions for the list columns.

An OCL variable is created for each search field in the SQL folder dialog. The naming convention is based on the OCL variables in Word reports: var<Feldname>.

When constructing the variable name based on the field name, only letters and numbers from the ASCII range are taken into account. All other symbols (e.g. spaces, hyphens, umlauts, etc.) are filtered out and not taken into account for the variable name.

Thus, a field with designation To Date results in a variable varToDate.

The created variables receive a corresponding OCL type according to the type of the search field:

• String: String
• Boolean: Boolean
• Date: DateTime
• Number: Float

For example, you want a list of projects with open services up to the cut-off date. This is already possible with the SQL expression bold_id IN (SELECT project FROM open service WHERE datum<='\1').

Now you also want to see the accrued fee up to the cut-off date in a column, and this is now possible with the new column variables:

Here in the example, the three columns from the effective date have the following expressions:

• Effective date: varEffective date
• Open fee until effective date: openPerformance->select(datum<=varStichdatum).wertext->sum
• Total open services: sumOpenServices

For fields without names (this is the default for SQL folders with only one query, without extra search field), the field name is Search. The resulting variable is then called varSearch. For example, the folder invoices (search). In this case, the column expression is: invoices->select(number=varSearch).

Using search field variables only works for single-query SQL folders. In multi-query SQL folders, the variables may show incorrect values (from the other queries) or display error messages (if variables are used that do not appear in all queries).