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

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.

Digits1) DIGITS converts a character string to numeric:

DIGITS (expression)

Example: DIGITS(NUMFLD)=CHARFLD

SELECT_SQL
    FIELDS((#STOCK) (#HLPSTK 'DIGITS(STOCK)'))
    FROM_FILES((ARTICLE))
ENDSELECT

Length2) LENGTH returns a field length in bytes

LENGTH (expression)

Example: LENGTH(CUSNAM)

SELECT_SQL
    FIELDS((#ARTDSC) (#HLPDSC 'LENGTH (ARTDSC)'))
    FROM_FILES((ARTICLE))
ENDSELECT

Square Root3) SQRT

Square root of a selected field

SQRT (expression)

SELECT_SQL
    FIELDS((#ARTPRS) (#HLPPRS 'SQRT(ARTPRS)'))
    FROM_FILES((ARTICLE))
ENDSELECT

Strip4) 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

Translate5) TRANSLATE

Convert the selected character string to capitals

TRANSLATE (expression)

SELECT_SQL
    FIELDS((#ARTDSC) (#HLPDSC 'TRANSLATE(ARTDSC)'))
    FROM_FILES((ARTICLE))
ENDSELECT