LANSA and SQL Tip : Scalar Built-In functions
| Date: | Archived |
|---|---|
| Product/Release: | LANSA - All Platforms |
| Abstract: | A few handy examples of scalar built-in functions in SQL |
| Submitted By: | LANSA Technical Support |
Description:
In SQL there are two sorts of built-in functions:
- Scalar functions These are functions relating to only one value.
- Column functions These are functions relating to several elements of one column.
Here are a few examples of the Scalar build in functions DIGITS, LENGTH, SQRT and STRIP.
1) DIGITS converts a character string to numeric:
DIGITS (expression)
Example: DIGITS(NUMFLD)=CHARFLD
SELECT_SQL
FIELDS((#STOCK) (#HLPSTK 'DIGITS(STOCK)'))
FROM_FILES((ARTICLE))
ENDSELECT
2) LENGTH returns a field length in bytes
LENGTH (expression)
Example: LENGTH(CUSNAM)
SELECT_SQL
FIELDS((#ARTDSC) (#HLPDSC 'LENGTH
(ARTDSC)'))
FROM_FILES((ARTICLE))
ENDSELECT
3) SQRT
Square root of a selected field
SQRT (expression)
SELECT_SQL
FIELDS((#ARTPRS) (#HLPPRS 'SQRT(ARTPRS)'))
FROM_FILES((ARTICLE))
ENDSELECT
4)
STRIP
Removes blanks or other selected characters from the beginning or end of a field.
STRIP (expression, position, strip character)
Possible values for the second parameter are:
LEADING (L) removes the chosen character from the beginning
of the selected field.
TRAILING (T) removes the chosen character from the end of the
selected field.
BOTH (B) removes the chosen character
from both the beginning and the end of the selected field.
If nothing is chosen, all blanks are automatically removed from the beginning and the end of the field..
SELECT_SQL
FIELDS((#ARTNO) (#HLPNO 'STRIP(ARTNO,LEADING,''0'')')
FROM_FILES((ARTICLE))
ENDSELECT
5)
TRANSLATE
Convert the selected character string to capitals
TRANSLATE (expression)
SELECT_SQL
FIELDS((#ARTDSC) (#HLPDSC
'TRANSLATE(ARTDSC)'))
FROM_FILES((ARTICLE))
ENDSELECT
