We have already discussed the creation of selections at runtime in an article. The focus of Artikels was the formulation of queries on linked tables. In today’s article, I would like to discuss the structure of logical expressions in dynamic selections.
When using dynamic selections, a logical expression is used to decide whether or not the data set is included in the selection set. To achieve this, conzept provides 16 different comparison operators (=, >, between[ …)]. The query itself is defined with function SelDefQuery(). As the instruction expects a string, there are a few points to consider when creating a logical expression.
Use of constants:
When using constants of type Alpha, please note that these are enclosed in double apostrophes (‘). Within instruction SelDefQuery(), these double apostrophes are changed to single apostrophes.
tHdlSel->SelDefQuery('', 'ART.aBezeichnung =* ''Software*''');Use of variables:
In general, it can be said that variables are not part of the query. However, this changes when the content of the variable is inserted into the query string. From this point on, the content of the variable is part of the query. If a variable of type Alpha is to be inserted within this, it must be enclosed in brackets with double apostrophes. Within the query string, the double quotation marks are changed to single quotation marks.
tHdlSel->SelDefQuery('', 'ART.aBezeichnung =* ''' + tValue + '''');If the variable is not available as an alpha type, it must be converted accordingly and also masked with apostrophes. This masking is converted accordingly within the instruction.
tHdlSel->SelDefQuery('', 'ART.iNummer >= ' + CnvAI(tValue));Special feature of the data types ‘Date’ and ‘Time’:
If the variable is of type Date or Time, there is a special feature worth noting here during conversion. The display of the time or date format usually depends on the country-specific settings of the operating system. If these are set to the format ‘YYYY-MM-DD’, for example, a conversion without specifying a format option returns the following string:
'KND.dEintrDatum = 2015-02-24'
Dieser String verursacht den Fehler Incorrect data type (_ErrParserWrongType), da an dieser Stelle zwingend das Datumsformat ‘day.month.year’ ('24.02.2015') erwartet wird. Um den Fehler zu vermeiden, ist bei der Konvertierung zwingend die Formatoption _FmtInternal anzugeben. Wird diese Formatoption angegeben, führt dies zu einem immer gleichen Ausgabeformat und dies unabhängig der länderspezifischen Einstellungen. Der String besitzt somit folgendes Aussehen: 'KND.dEintrDatum = 24.02.2015'
This string causes the error A (B), as the date format C (D) is expected at this point. To avoid this error, the format option E must be specified during conversion. If this format option is specified, this always results in the same output format, regardless of the country-specific settings. The string therefore has the following appearance: F
Variable of the type Date:
tHdlSel->SelDefQuery('', 'KND.dEintrDatum = ' + CnvAD(tValue, _FmtInternal));
Variable of the type Time:
tHdlSel->SelDefQuery('', 'KND.tEintrZeit = ' + CnvAT(tValue, _FmtInternal));It may also be necessary to specify format option _FmtInternalwhen converting numeric variables. The reason for this can also be found in the country-specific settings of the operating system. The default setting for numbers is digit grouping, which inserts a dot after every third number. If a conversion is now carried out without a format option, the dot is also inserted in the query string, which ultimately leads to error _ErrParserWrongType.