IBM 15 Conversion Functions, Comparison Functions

135

CLEM Language Reference

Conversion Functions

Conversion functions allow you to construct new ﬁelds and convert the storage type of existing

ﬁles. For example, you can form new strings by joining strings together or by taking strings apart.

Tojoin two strings, use the o perator ><. For example, if the ﬁeld Site has the valu e "BRAMLEY",

then"x x" >< Site returns "xxBRAMLEY". The result of >< is always a string, even if the arguments are

notst rings. Thus, if ﬁeld V1 is 3and ﬁeld V2 is 5, then V1 >< V2 returns"35" (a strin g, not a number).

Conversion functions (and any other functions that require a speciﬁc type of input, such as a

date or time value) depend on the current formats speciﬁed in the Stream Options dialog box.

For example, if you want to convert a string ﬁeld with values Jan 2003,Feb 2003, and so on,

select the matching date format MON YYYY as the default date format for the stream. For more

information, see the topic Setting general options for streams in Chapter 5 o n p. 55.

Function Result Description

ITEM1 >< ITEM2 String Concatenates values for two ﬁelds and returns the resulting

string as ITEM1ITEM2.

to_integer(ITEM) Integer Converts the storage of the speciﬁed ﬁeld to an integer.

to_real(ITEM) Real Converts the storage of the speciﬁed ﬁeld to a real.

to_number(ITEM) Number Converts the storage of the speciﬁed ﬁeld to a number.

to_string(ITEM) String Converts the storage of the speciﬁed ﬁeld to a string.

to_time(ITEM) Time Convertsthe storage of the speciﬁed ﬁeld to a time.

to_date(ITEM) Date Converts the storage of the speciﬁed ﬁeld to a date.

to_timestamp(ITEM) Timestamp Converts the storage of the speciﬁed ﬁeld to a timestamp.

to_datetime(ITEM) Datetime Converts the storage of the speciﬁed ﬁeld to a date, time,

or timestamp value.

datetime_date(ITEM) Date

Returns the date value for a number,string, or timestamp.

Note this is the only function that allows you to convert a

number (in seconds) back to a date. If ITEM is a string,

createsa date by parsinga string in the current date fo rmat.

The date format speciﬁed in the stream properties dialog

box must be correct for this function to be successful. If

ITEM isa number,it is interpreted asa number of seconds

since the base date (or epoch). Fractions of a day are

truncated. If ITEM is a timestamp, the date part of the

timestamp is returned. If ITEM is a date, it is returned

unchanged.

Comparison Functions

Comparison functions are used to compare ﬁeld values to each other or to a speciﬁed string. For

example, you can check strings for equality using =. An example of s tring equality veriﬁcation

is: Class = "class 1".

For purposes of numeric comparison, greater means closer to positive inﬁnity, and lesser

means closer to negative inﬁnity. That is, all negative numbers are less t han any positive number.

Function Result Description

count_equal(ITEM1,LIST) Integer

Returns the number of values from a list of ﬁelds that

are equal to ITEM1 or null if ITEM1 is null. Fo r more

information, see the topic Summarizing Multiple Fields in

Chapter 7 on p. 115.