LANSA and SQL Tip: Scalar built in functions(2)
|Product/Release:||LANSA - All Platforms|
|Abstract:||Few more handy examples of scalar built-in functions in SQL|
|Submitted By:||LANSA Netherlands|
SQL consists of two types of built in functions:
Scalaire functions These are functions which can use only one value.
Column functions These are functions which can use a number of elements in a column
Here are a few examples of the scalaire built in functions SUBSTR, CONCAT, MAX and MIN.
Returns the selected part of a character string.
SUBSTR (expression, integer, integer)
The first integer is used to define the start position of the string, the second integer is optional and can be used to define the length to be extracted.
FIELD = 'ASPECT COMPUTING (EUROPE)'
SUBSTR(FIELD, 10, 5) = 'MPUTI'
SUBSTR(FIELD, 18) = '(EUROPE)'
FIELDS((#ARTDES) (#SUBDES 'SUBSTR(ARTDES, 3, 5)'))
Strings two values together
String CONCAT string
FLDA CONCAT ‘ ‘ CONCAT FLDB
The values of FLDA and FLDB are strung together with one blank between them
FIELDS(#TEXT50 'CUSNAM CONCAT'' ''CONCAT CUSTWN')
It is also possible to combine SUBSTR:
FIELDS((#CUSNAM) (#CUSTWN) (#HLPVAR 'SUBSTR(CUSNAM, 5, 4) CONCAT SUBSTR(CUSTWN, 5, 8)'))
Returns the highest value of a range of given fields - at least two fields must be defined
MAX (expression, expression, etc)
When VALUE1 = 100,00
and VALUE2 = 35,75
and VALUE3 = 54,20
Returned value = 100,00.
Value of FLDA = JANSEN
Value of FLDB = AALBERS
Returned value = JANSEN.
The opposite of MAX
If the same examples as above are used and MAX is replaced by MIN, the returned values are 35,75 and AALBERS.
Return the record with the highest and lowest price:
FIELDS((#MIN ‘MIN(ARTPRS)') (#MAX ‘MAX(ARTPRS)'))