SQL expressions for SQL folders

Overview

Comparisons

Sorting

|| Operator / + Operator

Cast Operator

Queries

• Query of the logged-in user
• Query of normal fields
• Query of members
• Query blob fields
• Query date fields
• Query of custom items
• Query of keywords
• Query of tags
• Query of custom links
• Additional query of additional items on the additional class

Query Examples

• Addresses
• Invoices
• Services
• Projects
• Phases
• User
• Activities

Comparisons in SQL are performed in three ways:

• Exact comparison: ='\1'. The entire value entered must match the value you are looking for. Used mainly for numbers.
• Character comparison, start: like '\1%'. The entered value corresponds to the start of the searched value.
• Character comparison, part: like '%\1%'. The entered value corresponds to a part of the searched value.

The number 1 is the placeholder for the query that is queried by the user using the dialog. If more than one search field is defined, the numbers in the order 1,2,3...9 (a maximum of 9 fields can be defined) are used in the corresponding order of the search fields. If you do not have a query dialog, simply use the criteria you want to query instead of the placeholder.

Sorting

Sorting in the SQL folder is done by the List Settings . No sorting should be used in the SQL expression, as the data will be reordered by the list settings when displayed.

The || Operator is used in Firebird to connect text elements. The element before and after the || are interrelated.

extract(month from datum) || '.' || extract(year from datum)='\1'

In the example above, first the extracted month (e.g. 08) is taken, connected by a period, and then the extracted year (e.g. 2016) is appended as well. This results in a string of the form Month.Year (e.g. 08.2016).

The + operator is used on MS SQL Server to concatenate text elements. The element before and after the + are concatenated.

(cast(datepart(month,datum) as varchar)+'.'+cast(datepart(year,datum) as varchar)= '\1')

In the example above, first the extracted month (e.g. 08) is taken, connected by a period, and then the extracted year (e.g. 2016) is appended as well. This results in a string of the form Month.Year (e.g. 08.2016).

The Cast operator is used to convert a value to a specific data type. This is especially used when you need to work on the result, such as calculating numbers or adding a value to a date. The syntax is as follows:

CAST (value AS datatype)

Common data types are:

• timestamp (or date)
• Varchar
• integer
• numeric

The cast operator can be used to convert a string to a numeric value, e.g. a ZIP code on an address:

cast(standardplz as integer)

However, this is only possible if the content really contains only numeric values, otherwise you will get an error message. Therefore, you should check for numeric content at the same time. At the moment this is only possible with MS SQL server, unfortunately there is no isnumeric feature for Firebird. Example MS SQL server:

(isnumeric(standardplz)=1 and cast(standardplz as integer) >= 8000)

Query of the logged-in user

The string \login_id can be used to access the ID of the login within a SQL folder. For example, you can create a SQL folder with which the user can search in their managed addresses:

• Class: Address entry
• SQL: (Name like '%\1%' OR Alias like '%\1%') AND betreuer=\login_id

When querying normal fields, the easiest way to do this is to use the Insert Field... button in the SQL dialog. Normal fields are queried using one of the above comparisons. Example:

Betreffend like '%\1%'

SQL folder that, for example, shows all open services of an employee. This employee should be searched for.

• Class: OpenPower
• Query Title (free): User
• SQL expression: Bearbeiter in (SELECT Bold_ID from Projektbearbeiter WHERE Name like '%\1%')

BLOBs are used for fields that contain long text, such as the text of activities.

• Class: Activity
• Query Title (free): Text
• SQL-Expression Firebird: text containing '\1'
  (The statement must be inserted manually, since the selection dialog does not offer 'containing’ by default.)
• SQL Expression MS SQL Server: text like '%\1%'
  (MS SQL Server uses VARCHAR(MAX) instead of BLOB fields.)

This search is case-insensitive, meaning it finds uppercase, lowercase and any combination of the two.

Normal date fields, such as those found on invoices, services, etc., can be queried as follows:

• Datum='\1'
• Datum<'\1'
• Datum>'\1'

CreationDateTime>=cast('TODAY' as timestamp)-\1

CreationDateTime>=GETDATE()-\1

extract(year from datum)=\1

cast(datepart(year,datum) as varchar) = '\1'

datum>=cast('TODAY' as date)-\1

(DATEDIFF(day, datum, getdate()) < \1) and (datum <= getdate())

In addition, there are special date fields in Vertec, which also save a part of time. These are, for example, the CreationDateTime or ModifiedDateTime, which are saved on each entry.

An exact date comparison cannot be made here, because a date with a time part is always larger than the same date without a time part. The way around this is to add a day to the entered To date and make a < comparison. For the From date, you do not have to add anything. So the result is a single day (if you want to know which entries were created or changed on which day). The date is queried by the user.

(CreationDateTime >= '\1') AND (CreationDateTime < CAST('\1' 
AS TIMESTAMP) + 1)

('\1' = '' OR CreationDateTime >= '\1') AND ('\2' = '' OR 
(CreationDateTime < CAST('\2' AS TIMESTAMP) + 1))

CreationDateTime < '\1' OR CreationDateTime >= '\1'+1

creationdatetime>='\1' and creationdatetime<cast('\1' as 
datetime)+1

creationdatetime>='\1' and creationdatetime<cast
('\2' as datetime)+1

creationdatetime<'\1' or creationdatetime>=cast('\1' as datetime)+1

The query of custom fields via SQL folder takes place to the following scheme, depending on the type of the custom field :

• SQL expression: bold_id IN (SELECT usereintrag FROM zusatzfeld WHERE metazusatzfeld=<ID> AND <Query><=Comparison>)

This SQL expression can be used equally for all custom fields. The parts in <brackets> must be customized:

• <ID>: This is the internal ID of the custom field:

  

• <Query>: Depending on the field type, the query has to be formulated differently. A complete list of queries can be found here in the article on the custom fields.
• <Comparison>: The comparisons can be done in three ways: =\1, like '\1%', and like '%\1%'. The 1 wildcard represents the value entered by the user in the search dialog. If you are showing a SQL folder without a search dialog, you must enter a specific value here. See the Comparisons section earlier in this article when to use which type of comparison.

Keywords are folders whose parent folder is called keyword folder. The query searches these subfolders for the designation. For the query, the Internal Id of the keyword folder, i.e. the parent folder, must be specified, in the following example Projects by branch:

The comparisons can be made in three ways: =\1, like '\1%', and like '%\1%'. The placeholder 1 represents the value entered by the user in the search dialog. For a SQL folder without a search dialog, a specific value must be entered here. See the Comparisons section above when and what type of comparison should be used. Comparisons

In the example, the keyword search folder is located in the root folder Public Folders:

• Class: Project
• Field name: Branch
• Field type: Symbol
• SQL expression: bold_id IN (select ordeintraege from ordnerlink where ordner in (select bold_id from abstractordner where parentordner = 14729 AND bezeichnung like '%\1%'))

You can also query tags in SQL folders. The query is done in the same way as for the other fields with one of the above Comparisons :

tags like '%\1%'

As an example, the custom link type Board of directors-VRMandates (Person-Company) is used. A SQL folder is created where VRMandates can be searched for board of directors:

• Class: Company
• Query Name (Example): Board of Directors
• SQL expression:

  BOLD_ID in (SELECT CustomLinks FROM CustomLink WHERE FromCustomLinks in (SELECT BOLD_ID FROM Adresseintrag WHERE Name like '\1%') AND Linktyp = 28987)

• If the link type is VRMandate Board Members, so the link roles are defined the other way round, the expression is instead:

  BOLD_ID in (SELECT FromCustomLinks FROM CustomLink WHERE CustomLinks in (SELECT BOLD_ID FROM Adresseintrag WHERE Name like '\1%') AND Linktyp = 28987)

This query returns all companies with which the requested person is associated. The Bold_ID corresponds to the Internal Id found in the properties dialog.

The query of custom links from normal classes to custom classes and vice versa is exactly the same. Something special is the additional query of custom fields on such custom classes.

As an example, the CustomClass12 is used, with which the installed version is stored at the customer (address) via a custom link. The custom link looks like this:

To show all customer addresses that have a version, the query is:

BOLD_ID in (SELECT CustomLinks FROM CustomLink WHERE Linktyp = 58973)

To show all objects of the CustomClass12 to which a customer is assigned, the query reads:

BOLD_ID in (SELECT FromCustomLinks FROM CustomLink WHERE Linktyp = 58973)

The Internal Id of the link type must be specified as BOLD_ID.

In principle, custom fields on custom classes are queried in the same way as custom fields on normal classes – simply specify the corresponding custom class (e.g. custom class12).

To continue with the above example, we will describe an example that can be used to find all addresses that have a specific version installed. For this purpose, we will look for the custom field version on the custom class:

BOLD_ID in (SELECT CustomLinks FROM CustomLink WHERE ((Linktyp = 58973) and (FromCustomLinks in 
(SELECT usereintrag FROM zusatzfeld WHERE metazusatzfeld=58974 AND wertinteger=124))))

Custom fields can be queried like the normal custom items (see section Sql expression depending on custom field type ), here for example with wertinteger because it is an integer field.

The above example returns all addresses that have version 124 installed. The BOLD_ID of the custom field corresponds to the Internal Id of the version custom field:

The search field queries the name of the company for which the counterparties are to be listed.

• Show search dialog: yes
• Class: Adresseintrag
• SQL expression:
  bold_id in (select gegenprojekte from Projektgegenparteilink where gegenparteien in (select bold_id from adresseintrag adr where adr.name like '%\1%'))

• Show search dialog: no
• Class: Rechnung
• SQL expression Firebird:
  (extract (month from datum) = extract (month from cast('TODAY' as date))) and (extract (year from datum) = extract (year from cast('TODAY' as date)))
  SQL Expression MS SQL Server:
  (cast(datepart(month, datum) as integer) = cast(datepart(month, GETDATE()) as integer)-1) AND (cast(datepart(year, datum) as integer) = cast(datepart(year, GETDATE()) as integer))

• Show search dialog: no
• Class: Rechnung
• SQL expression Firebird:
  (extract (month from datum) = extract (month from cast('TODAY' as date))-1) and (extract (year from datum) = extract (year from cast('TODAY' as date)))
• SQL expression MS SQL Server:
  (cast(datepart(month, datum) as integer) = cast(datepart(month, GETDATE()) as integer)-1) AND (cast(datepart(year, datum) as integer) = cast(datepart(year, GETDATE()) as integer))

If the system setting To date for invoice include value date = True:

• Show search dialog: yes
• Class: Invoice
• SQL expression: (case when xvalutadatum is null THEN (case when bis is null THEN datum else bis END) ELSE xvalutadatum END) <='\1'

If the system setting To date for invoice include value date = False:

• Show search dialog: yes
• Class: Invoice
• SQL expression: (case when xvalutadatum is null THEN datum else xvalutadatum END) <='\1'

• Show search dialog: yes
• Class: Invoice
• SQL-Expression Firebird: (bezahlt = 0) and (datum<(cast('TODAY' as date)-'\1'))
• SQL Expression MS SQL Server: (bezahlt = 0) and (datum<(getdate()-'\1'))

• Show search dialog: yes
• Class: Rechnung
• SQL expression Firebird: extract(month from datum) || '.' || extract(year from datum)=\1
• SQL expression MS SQL Server: cast(datepart(month, datum) as varchar) + '.' + cast(datepart(year,datum) as varchar) = '\1'

The invoice must belong to a project with a specific code pattern. The code is queried.

• Show search dialog: yes
• Class: Rechnung
• SQL expression: projekt in (select bold_id from projekt where code like '%\1%')

In the first part, the project type is accessed by the project and in the second part, a specific project code is excluded. Since the SQL not like does not work for these queries, a subquery excludes the corresponding part.

• Show search dialog: no
• Class: Rechnung
• SQL expression: projekt in (select bold_id from projekt where projekt.typ = 18710) AND projekt not in (select bold_id from projekt where code like 'PD%')

where the number 18710 in the example corresponds to the Internal Id of the corresponding project type. PD% means that all projects with a code starting with PD are excluded.

All invoices of a project leader should be shown. In the example, the abbreviation of the project leader is queried.

• Show search dialog: yes
• Class: Invoice
• SQL expression: projekt in (SELECT bold_id from Projekt WHERE Projektleiter in (select bold_id from Projektbearbeiter where kuerzel like '%\1%'))
• SQL expression if only the open invoices of a project leader are to be shown: projekt in (SELECT bold_id from Projekt WHERE Projektleiter in (select bold_id from Projektbearbeiter where kuerzel like '%\1%')) and verrechnet=0

This SQL folder shows as a result a list of all (open and invoiced) services that have been provided on a specific day of the week.

• Show search dialog: no
• Class: Service
• SQL-Expression Firebird: ((extract (weekday from datum))=6) or((extract (weekday from datum))=0)
  As an example, this expression returns services that were performed on either a Saturday or a Sunday.
• SQL Expression MS SQL Server: (datepart(weekday,datum)=6)or((datepart(weekday,datum)=7))
  As an example, this expression returns services that were performed on either a Saturday or a Sunday.

The days of the week in the query are assigned to the following numbers:

• 0 = Sunday
• 1 = Monday
• 2 = Tuesday
• 3 = Wednesday
• 4 = Thursday
• 5 = Friday
• 6 = Saturday
• 7 = Sunday

This SQL folder shows as a result a list of all services that have been provided in a certain period of time.

• Show search dialog: yes
• Class: Service

Services on a given day

• SQL expression: date = '\1'
• Enter 'dd.mm.yyyy’, e.g. '01.01.2014'

Services of a month

• SQL-Expression Firebird: extract(year from datum) = \1
• SQL Expression MS SQL Server: cast(datepart(year , datum) as varchar) = '\1'
• Enter 'mm.yyyy’, e.g. '01.2014'

Services for one year

• SQL-Expression Firebird: extract(year from datum) = \1
• SQL Expression MS SQL Server: cast(datepart(year , datum) as varchar) = '\1'
• Enter 'yyyy’, e.g. '2014'

This SQL folder shows as a result a list of all services (open and billed) assigned to a specific type of service. In the query dialog you can specify the type of service.

• Show search dialog: yes
• Class: Service
• SQL expression: typ in (select bold_id from projekteintragstyp where (bold_type=22) and (code like '\1%'))

This works analogously for outlays (bold_type=281) and expenses (bold_type=23).

This SQL folder shows as a result a list of open and invoiced services, which were entered on days with absences.

• Show search dialog: yes
• Class: Service
• Search fields: From (date), To (date)

bold_id in (select bold_id from offeneleistung ol where
 datum >= '\1' and datum <= '\2' and
 (
 (select count(*) from abwesenheit abw1 where minutenabwesend is null and ol.bearbeiter = abw1.bearbeiter and ol.datum >= abw1.datum and ol.datum <= abw1.bisdatum) > 0
 or
 (select count(*) from abwesenheit abw3 where
 abw3.minutenabwesend is null and ol.datum >= abw3.datum and ol.datum <= abw3.bisdatum and
 abw3.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = ol.bearbeiter)
 ) > 0
 )
)
or 
bold_id in (select bold_id from verrechneteleistung vl where
 datum >= '\1' and datum <= '\2' and
 (
 (select count(*) from abwesenheit abw2 where minutenabwesend is null and vl.bearbeiter = abw2.bearbeiter and vl.datum >= abw2.datum and vl.datum <= abw2.bisdatum) > 0
 or
 (select count(*) from abwesenheit abw4 where
 abw4.minutenabwesend is null and vl.datum >= abw4.datum and vl.datum <= abw4.bisdatum and
 abw4.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = vl.bearbeiter)
 ) > 0
 )
)

• Requires an additional search field: Also search in hourly absences? (Boolean)

bold_id in (select bold_id from offeneleistung ol where
 datum >= '\1' and datum <= '\2' and
 (
 (select count(*) from abwesenheit abw1 where (minutenabwesend is null or minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and ol.bearbeiter = abw1.bearbeiter and ol.datum >= abw1.datum and ol.datum <= abw1.bisdatum) > 0
 or
 (select count(*) from abwesenheit abw3 where
 (abw3.minutenabwesend is null or abw3.minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and ol.datum >= abw3.datum and ol.datum <= abw3.bisdatum and
 abw3.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = ol.bearbeiter)
 ) > 0
 )
)
or 
bold_id in (select bold_id from verrechneteleistung vl where
 datum >= '\1' and datum <= '\2' and
 (
 (select count(*) from abwesenheit abw2 where (minutenabwesend is null or minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and vl.bearbeiter = abw2.bearbeiter and vl.datum >= abw2.datum and vl.datum <= abw2.bisdatum) > 0
 or
 (select count(*) from abwesenheit abw4 where
 (abw4.minutenabwesend is null or abw4.minutenabwesend < (CASE WHEN \3=1 THEN 9999 ELSE 0 END)) and vl.datum >= abw4.datum and vl.datum <= abw4.bisdatum and
 abw4.bearbeiter in (select gruppen from benutzergruppenlink where benutzer = vl.bearbeiter)
 ) > 0
 )
)

SQL folder that outputs all projects that have open services until a certain date.

• Class: Project
• Show search dialog: yes
• Expression:
  bold_id in (select projekt from offeneLeistung where Datum<='\1')

Attention: Already charged services are not taken into account, even if the service was still open on the entered date.

SQL folder that finds all projects that had no open or billed services since a certain date, but are still active. The effective date is specified in the query dialog.

• Class: Project
• Show search dialog: yes
• Expression:
  aktiv=1 and bold_id not in (select projekt from offeneLeistung where Datum>='\1') and bold_Id not in (select projekt from verrechneteLeistung where Datum >='\1')

SQL folder that outputs all productive projects that still have open services.

• Class: Project
• Show search dialog: no
• Firebird and MS SQL Server:
  typ in (select bold_id from projekttyp where produktiv=1) and bold_id in (SELECT projekt from offeneleistung)

SQL folder that shows all phases and subphases of a project. In the example, the project code is queried.

• Class: Project phase
• Show search dialog: yes
• Expression:
  (projekt in (select bold_id from projekt where code like '%\1%')) or (parentphase in (select ph.bold_id from projektphase ph where ph.projekt in (select bold_id from projekt where code like '%\1%')))

This expression only works for the first level of Sub-Phase . Subphases of subphases are not considered.

The difficulty is that subphases know only the phase to which they are attached, but not the project. So it is necessary to look for both the project and the project of the parent phase.

Since the same table (project phase) is addressed twice for different requests, it is necessary to work with aliases (select ph.bold_id from projektphase ph where...)

SQL folder that displays the presence times by user in a specified date range.

The default values are the currently logged-in user and the last month. In the search dialog, this query can also be formulated differently.

• Class: Praesenzzeit (must be entered manually in the field)
• Show search dialog: yes
• Expression:
  bold_id in (select bold_id from praesenzzeit where bearbeiter in (select bold_id from projektbearbeiter where UPPER(kuerzel) = UPPER('\1'))) and datum>='\2' and datum<='\3'

Three Search Fields must be defined in the query:

The following columns must be defined in the List Settings :

• Class: Activity
• Show search dialog: yes
• (optional) Icon : 82
• SQL-Expression Firebird:
  KontaktArt=1 and Titel like '%\1%' and text containing '\2' and EmailSender like '%\3%' and EmailRecipients like '%\4%'
• SQL Expression MS SQL Server:
  KontaktArt=1 and Titel like '%\1%' and text like '%\2%' and EmailSender like '%\3%' and EmailRecipients like '%\4%'

Three search fields must be defined in the query:

SQL folder for searching for activities with the criteria title, contact, date from-to, file name, project (code, description, regarding) and activity type.

• Class: Activity
• Show search dialog: yes
• Expression: see further down

Titel containing '\1' and Pfad containing '\5' and
(
BOLD_ID = (case when CHARACTER_LENGTH('\2') = 0 then bold_id else - BOLD_ID end) or
adresseintrag in (select BOLD_ID from Adresseintrag where Alias containing '\2')
)
and
(
BOLD_ID = (case when CHARACTER_LENGTH('\3') = 0 then bold_id else - BOLD_ID end) or
CAST(Datum as Date) >= CAST('\3' as Date)
)
and
(
BOLD_ID = (case when CHARACTER_LENGTH('\4') = 0 then bold_id else - BOLD_ID end) or
CAST(Datum as Date) < CAST('\4' as Date)+1
)
and
(
BOLD_ID = (case when CHARACTER_LENGTH('\6') = 0 then bold_id else - BOLD_ID end) or
projekt in (select BOLD_ID from Projekt where Code containing '\6' or Beschrieb containing '\6' or Betreffend containing '\6')
)
and
(
BOLD_ID = (case when CHARACTER_LENGTH('\7') = 0 then bold_id else - BOLD_ID end) or
typ in (select BOLD_ID from AktivitaetsTyp where bezeichnung containing '\7')
)

Titel like '%\1%' and Pfad like '%\5%' and
(
BOLD_ID = (case when len('\2') = 0 then bold_id else - BOLD_ID end) or
adresseintrag in (select BOLD_ID from Adresseintrag where Alias like '%\2%')
) 
and 
(
BOLD_ID = (case when len('\3') = 0 then bold_id else - BOLD_ID end) or
Datum >= '\3' or Datum is Null
)
and
(
BOLD_ID = (case when len('\4') = 0 then bold_id else - BOLD_ID end) or
Datum < cast('\4' as datetime)+1 or Datum is null
)
and
(
BOLD_ID = (case when len('\6') = 0 then bold_id else - BOLD_ID end) or
projekt in (select BOLD_ID from Projekt where Code like '%\6%' or Beschrieb like '%\6%' or Betreffend like '%\6%')
)
and 
(
BOLD_ID = (case when len('\7') = 0 then bold_id else - BOLD_ID end) or
typ in (select BOLD_ID from AktivitaetsTyp where bezeichnung like '%\7%')
)