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 ]