DB2 Server for VSE & VM: Quick Reference

Functions

The two kinds of functions are column and scalar.

Column Functions

Produce a value from an argument having a collection of values that are derived from one or more columns.

AVG

Calculates the average of a group of column values. 

              .-ALL-.
>>-AVG---(--+-+-----+--numeric_exp---+---)---------------------><
            '-DISTINCT--column_name--'

COUNT

Obtains the number of rows or distinct values in a collection of rows or column values. 

>>-COUNT----+-(--DISTINCT--column_name--)--+-------------------><
            '-(*)--------------------------'

MAX

Obtains the maximum value in a collection of column values. 

               .-ALL-.
>>-MAX--(----+-+-----+--exp----------------+--)----------------><
             |          (1)                |
             '-DISTINCT-------column_name--'

Notes:

  1. Although the keyword DISTINCT is allowed, it does not affect the result of the function.

MIN

Obtains the minimum value in a set of column values. 

               .-ALL-.
>>-MIN--(----+-+-----+--exp----------------+--)----------------><
             |          (1)                |
             '-DISTINCT-------column_name--'

Notes:

  1. Although the keyword DISTINCT is allowed, it does not affect the result of the function.

SUM

Obtains the total of all values in a group. 

               .-ALL-.
>>-SUM--(----+-+-----+--numeric_exp---+--)---------------------><
             '-DISTINCT--column_name--'

Scalar Functions

Produce a single value from an argument having a single value.

CHAR

Creates a character representation of certain noncharacter data types. 

>>-CHAR--(----+-decimal_exp----------------+--)----------------><
              +-timestamp_exp--------------+
              '--+-date_exp-+---+--------+-'
                 '-time_exp-'   +-,ISO---+
                                +-,USA---+
                                +-,EUR---+
                                +-,JIS---+
                                '-,LOCAL-'

DATE

Creates a date from an expression or timestamp. 

>>-DATE----(exp)-----------------------------------------------><

DAY

Extracts the day part of a value. 

>>-DAY--(----+-date_exp----------+--)--------------------------><
             +-timestamp_exp-----+
             +-date_dur_exp------+
             '-timestamp_dur_exp-'

DAYS

Extracts an integer representation of a date. The result is one more than the number of days from January 1, 0001, to the date. 

>>-DAYS--(----+-date_exp------+--)-----------------------------><
              +-timestamp_exp-+
              '-date_str_exp--'

DECIMAL

Returns a decimal representation of a numeric value. 

>>-DECIMAL--(--numeric_exp-------------------------------------->
 
>-----+-----------------------------------------+--)-----------><
      |                     .-,0-------------.  |
      '-,precision_integer--+----------------+--'
                            '-,scale_integer-'

DIGITS

Returns a character string representation of a number without a sign or decimal point. 

>>-DIGITS--(----+-integer_exp-+--)-----------------------------><
                '-decimal_exp-'

FLOAT

Returns a floating-point representation of a number. 

>>-FLOAT----(numeric_exp)--------------------------------------><

HEX

Returns a hexadecimal representation of a value. 

>>-HEX----(exp)------------------------------------------------><

HOUR

Extracts the hour part of a value. 

>>-HOUR--(----+-time_exp----------+--)-------------------------><
              +-timestamp_exp-----+
              +-time_dur_exp------+
              '-timestamp_dur_exp-'

INTEGER

Returns an integer representation of a number. 

>>-INTEGER----(numeric_exp)------------------------------------><

LENGTH

Returns the length of a value. 

>>-LENGTH----(exp)---------------------------------------------><

MICROSECOND

Extracts the microsecond part of a value. 

>>-MICROSECOND--(----+-timestamp_exp-----+--)------------------><
                     '-timestamp_dur_exp-'

MINUTE

Extracts the minute part of a value. 

>>-MINUTE--(----+-time_exp----------+--)-----------------------><
                +-timestamp_exp-----+
                +-time_dur_exp------+
                '-timestamp_dur_exp-'

MONTH

Extracts the month part of a value. 

>>-MONTH--(----+-date_exp----------+--)------------------------><
               +-timestamp_exp-----+
               +-date_dur_exp------+
               '-timestamp_dur_exp-'

SECOND

Extracts the seconds part of a value. 

>>-SECOND--(----+-time_exp----------+--)-----------------------><
                +-timestamp_exp-----+
                +-time_dur_exp------+
                '-timestamp_dur_exp-'

STRIP

Removes blanks or another specified character from the end or the beginning of a string. 

>>-STRIP--(----------------------------------------------------->
 
                       .-,B--,' '-----------------------.
>-----+-character_str--+--------------------------------+-+--)--><
      |                |           .-,' '-------------. | |
      |                '--+-,L-+---+------------------+-' |
      |                   +-,T-+   '-,strip_character-'   |
      |                   '-,B-'                          |
      |              .-,B--,'G< >'--------------------.   |
      '-graphic_str--+--------------------------------+---'
                     |           .-,'G< >'----------. |
                     '--+-,L-+---+------------------+-'
                        +-,T-+   '-,strip_character-'
                        '-,B-'

SUBSTR

Returns a part (substring) of string as indicated by the length and starting position. 

>>-SUBSTR---(--str_exp,--start_integer_exp---------------------->
 
>----+---------------------+---)-------------------------------><
     '-,length_integer_exp-'

TIME

Creates a time from a value. 

>>-TIME--(----+-time_exp------+--)-----------------------------><
              +-timestamp_exp-+
              '-time_str_exp--'

TIMESTAMP

Creates a timestamp from a value or a pair of values that represent a date and time. 

>>-TIMESTAMP---------------------------------------------------->
 
>----(--+-+-timestamp_exp----------------+------+---)----------><
        | +-timestamp_str_exp------------+      |
        | +-yyyymmddhhmmss_character_exp-+      |
        | '-370_storeclock_character_exp-'      |
        '--+-date_exp-----+---+-,time_exp-----+-'
           '-date_str_exp-'   '-,time_str_exp-'

TRANSLATE

Changes one or more characters in a string expression into other characters. For example, it can be used to reorder characters in a string to uppercase. 

>>-TRANSLATE---(--+-character_str_exp--| parameters-a |--+-----><
                  +-graphic_str_exp--| parameters-b |----+
                  '-)------------------------------------'
 
parameters-a
 
    .-,'ABC...XYZ',----'abc...xyz'-------------------------------------.
|---+----------------------------------------------------+------|
    |              .-,X'000102...FDFEFF'---------------. |
    '-,to_str_exp--+-----------------------------------+-'
                   |                .-,' '-----------. |
                   '-,from_str_exp--+----------------+-'
                                    '-,pad_character-'
 
parameters-b
 
                                .-,G'< >'--------.
|---,to_str_exp--,from str_exp--+----------------+--------------|
                                '-,pad_character-'

VALUE

Returns the first nonnull result in a series of SQL expressions. 

                    .---------.
                    V         |
>>-VALUE--(--exp-------,exp---+--)-----------------------------><

VARGRAPHIC

Returns a graphic string representation of a character string. 

>>-VARGRAPHIC----(exp)-----------------------------------------><

YEAR

Extracts the year part of a value. 

>>-YEAR--(----+-date_exp----------+--)-------------------------><
              +-timestamp_exp-----+
              +-date_dur_exp------+
              '-timestamp_dur_exp-'

[ Top of Page | Previous Page | Next Page | Table of Contents ]