Using Functions

How to:

When you click the Functions tab in the Transformation, Filter, or SQL calculators, a list of available functions appears. Right-click the functions tab to display them alphabetically or by category.

When using functions in a calculator, the Function Assist dialog box can help you set the parameters.

Note: All functions may not be available on all platforms.

Function

Arguments

Description

Character Functions

ARGLEN
(length, 
source_string, 
output_format)

Measures the length of a character string within a field, excluding trailing blanks.

ASIS
(argument)

Distinguishes between a space and a zero.

Note: This function is only available for variables and appears only in the User Variables calculator.

CHKFMT
(numchar,  
source_string, 'mask',  
output_format)

Checks numeric and alphanumeric fields for invalid character types.

CTRAN
(length,  
source_string,  
decimal, decvalue,  
output_format)

Replaces characters in a string, taking decimal ASCII values as arguments for the target.

CTRFLD
(source_string,  
length, output_format)

Centers a character string within a field.

EDIT
(source_string

Converts between alphanumeric and integer data.

EDIT
(source_string, 
'mask')

Using a mask, extracts characters from an alphanumeric string and inserts new characters.

GETTOK
(source_string,  
length, token_number, 
'delim', outlen,  
output_format)

Divides a string into tokens and returns the specified token.

LCWORD
(length,  
source_string,  
output_format)

Converts the letters in a given string to mixed-case: the first letter of each new word and the first letter after a single or double quotation mark are converted to uppercase. The rest are converted to lowercase.

LCWORD2
(length,  
source_string,  
output_format)

Converts a character string to mixed-case. The first letter of every word is converted to uppercase.

LCWORD3
(length,  
source_string,  
output_format)

Converts a character string to mixed-case; the first letter after every apostrophe remains capitalized and every other letter is converted to lowercase, unless it is the last letter of the word. If it is the last letter, it will be converted to lowercase.

LJUST
(length,  
source_string,  
output_format)

Left-justifies a character string within its field.

LOCASE
(length,  
source_string,  
output_format)

Returns the infield in lowercase.

OVRLAY
(source_string,  
length, substring,  
sublen, position,  
output_format)

Overlays a substring to a specified place on another character string.

PARAG
(length,  
source_string, 
'delim',  
max_token_size,  
output_format)

Divides lines of text into smaller lines by marking them off with a delimiter character.

PATTERN 
(length,  
source_string,  
output_format)

Converts text to its pattern.

POSIT
(source_string,  
length, substring,  
sublength,  
output_format)

Finds the starting position of a substring within a larger string.

RJUST
(length,  
source_string,  
output_format)

Right-justifies a character string within a field.

SOUNDEX
(length,  
source_string,  
output_format)

Converts character strings to 4-letter codes, enabling you to search for character strings phonetically without knowing how they are spelled.

SPELLNM
(outlength, number,  
output_format)

Takes a number with two decimal spaces and spells it out with dollars and cents.

SQUEEZ
(length,  
source_string,  
output_format)

Reduces multiple contiguous blank characters within an input string to a single blank character.

STRIP
(length,  
source_string, char,  
output_format)

Removes all occurrences of a specific character from an input string.

STRREP
(inlength, instring, 
searchlength, 
searchstring,
replength, repstring, 
outlength, 
output_format)

Replaces character strings.

SUBSTR
(length,  
source_string, start,  
end, sublength,  
output_format)

Extracts a substring of a given length and starting position from a larger string.

TRIM
(trim_where,  
source_string, length,  
pattern, sublength,  
output_format)

Removes leading and/or trailing occurrences of a pattern within a string.

TRUNCATE
(ARGUMENT)

Removes trailing blanks from dialog Manager amper variables and adjusts the length accordingly.

Note: This function is only available for variables and appears only in the User Variables calculator.

UPCASE
(length,  
source_string,  
output_format)

Converts all characters in a string to uppercase.

Character Functions - DBCS Code Pages

DCTRAN
(length, source_string, decimal, decvalue, output_format)

Translates a single-byte or double-byte character within a character string to another character based on its decimal value.

DEDIT
(length, source_string, mask_length, 'mask', output_format)

DEDIT function to extract characters from or add characters to a string when server is configured to use a DBCS code page.

DPART
(date, 'component', output_format)

Retrieve a Date Component as a Numeric Value.

DSTRIP
(length, source_string, char, output_format)

Removes all occurrences of a specific single-byte or double-byte character from a string.

DSUBSTR
(length, source_string, start, end, sublength, output_format)

Extracts a substring based on its length and position in the parent string when server is configured to use a DBCS code page.

Character Functions - Variable Length

LENV
(source_string,  
output_format)

Returns the actual length of an AnV input field or the size of an An field.

LOCASV
(upper_limit,  
source_string,  
output_format)

Converts alphabetic characters to lowercase and is similar to LOCASE.

POSITV
(source_string,  
upper_limit,  
substring, sub_limit,  
output_format)

Finds the starting position of a substring within a larger string and is similar to POSIT.

SUBSTV
(upper_limit,  
source_string, start,  
sub_limit,  
output_format)

Extracts a substring from a string and is similar to SUBSTR.

TRIMV
(trim_where,  
source_string,  
upper_limit, 
pattern,  
pattern_limit,  
output_format)

Removes a pattern from a string and is similar to TRIM.

UPCASV
(upper_limit,  
source_string,  
output_format)

Converts alphabetic characters to uppercase and is similar to UPCASE.

Data Source and Decoding Functions

DB_LOOKUP
(lookupsynonym, 
lookupcolumn1, 
sourcecolumn1,
lookupcolumn2, 
sourcecolumn2 ...  
returncolumn)

Looks up values in a table and returns the value from another column of the row where the first value was found.

For more information on improving lookup performance, see Improving Lookup Performance.

DB_EXPR
DB_EXPR(native_SQL_expression)

Inserts a native SQL expression exactly as entered into the native SQL generated for a FOCUS or SQL language request.

DB_INFILE
DB_INFILE(target_file, s1, t1, ... sn, tn)

Compares one or more field values in a source file to values in a target file

DB_SUBQUERY_IN
 

DB_SUBQUERY_NOTIN
 

DECODE
fieldname(code1
result1... [ELSE
default])

Changes a variable to an associated name.

LAST 
fieldname

Retrieves the previous value for a field.

Date Functions - Legacy

AYM
(indate, months,  
output_format)

Adds or subtracts months from dates stored in alphanumeric or integer format.

AYMD
(indate, days,  
output_format)

Adds or subtracts a given number of days to a date in [YY]YYMMDD format.

CHGDAT
('in_display_options',
'out_display_options',  
date_string,  
output_format) 

Rearranges the year, month, and day portions of an input character string representing a date. It may also convert the input string from long to short or short to long date representation.

DADMY
(indate,  
output_format)

Returns date in day-month-year format given the number of days since 1/1/1900.

DADYM
(indate,  
output_format)

Given the number of days since 1/1/1900, returns date in day-year-month format.

DAMDY
(indate,  
output_format)

Returns date in month-day-year format given the number of days since 1/1/1900.

DAMYD
(indate,  
output_format)

Returns date in month-year-day format given the number of days since 1/1/1900.

DADYM
(indate,  
output_format)

Returns date in year-day-month format given the number of days since 1/1/1900.

DAYMD
(indate,  
output_format)

Returns date in year-month-day format given the number of days since 1/1/1900.

DMY
(from_date, to_date)

Returns the difference between two dates in day-month-year order.

DOWK
(indate,  
output_format)

Returns the day of the week in A4 format.

DOWKL
(indate,  
output_format)

Returns the day of the week in A12 format.

DTDMY
(number,  
output_format)

Given the date in day-month-year format, calculates the number of days since 1/1/1900.

DTDYM
(number,  
output_format)

Calculates the number of days since 1/1/1900 given the date in day-year-month format.

DTMDY
(number,  
output_format)

Calculates the number of days since 1/1/1900 given the date in month-day-year format.

DTMYD
(number,  
output_format)

Calculates the number of days since 1/1/1900 given the date in month-year-day format.

DTYDM
(number,  
output_format)

Calculates the number of days since 1/1/1900 given the date in year-day-month format.

DTYMD
(number,  
output_format)

Calculates the number of days since 1/1/1900 given the date in year-month-day format.

GREGDT
(indate,  
output_format)

Converts a Julian date to a Gregorian date.

JULDAT
(indate,  
output_format)

Converts a Gregorian date to a Julian date.

MDY
(from_date, to_date)

Returns the difference between two dates in month-day- year order.

TODAY
(output_format)

Returns the current date in alphanumeric format (MM/DD/YYYY).

YM
(from_date, to_date,  
output_format)

Returns the number of days between two dates.

YMD
(from_date, to_date)

Returns the difference between two dates in year-month-day order.

Date Functions - Standard

DATEADD
(date, component_code,  
increment)

Adds or subtracts years, months, or days to or from a date.

DATECVT
(date, in_format,  
out_format)

Converts dates from one date format to another.

DATEDIF
(from_date, to_date,  
component_code)

Calculates the difference between two dates, expressed as years, months, or days.

DATEMOV
(date, 'move-point')

Moves a date to a significant point on the calendar.

DATETRAN
(indate, '(intype)', 
'(formatops)', 'lang',  
outlen, output)

Formats dates in international formats.

Date-Time Functions

HADD
(timestamp, component,  
increment, length,  
output_format)

Increments a date-time field by a given number of units.

HCNVRT
(timestamp,(format),  
length, output_format)

Converts a date-time field to alphanumeric format for use with operators, such as EDIT, CONTAINS, and LIKE.

HDATE
(timestamp,  
output_format)

Extracts the date portion of a date-time field and converts it to a date format.

HDIFF
(end_timestamp,  
start_timestamp,  
component,  
output_format)

Finds the number of boundaries of a given type crossed going from date 2 to date 1.

HDTTM
(date, length,  
output_format)

Converts a date field to a date-time field. The time portion is set to midnight.

HEXTR
(source, 
'componentstring', 
length, output_format)

Extracts components of a date-time value and sets remaining components to zero.

HGETC
(length,  
output_format)

Stores the current date and time in a date-time field.

HHMMSS
(output_format)

Returns the current time in alphanumeric format.

HINPUT
(source_length,  
source_string,  
timestamp_length,  
output_format)

Converts an alphanumeric string to a date-time value.

HMASK
(source, 
'componentstring', 
input, length, 
output_format)

Extracts components of a date-time value and preserves remaining components.

HMIDNT
(timestamp, length,  
output_format)

Changes the time portion of a date-time field to midnight (all zeroes).

HNAME
(timestamp, component,  
output_format)

Extracts a specified component from a date-time field and returns it in alphanumeric format.

HPART
(value, component,  
output_format)

Extracts a specified component from a date-time field and returns it in numeric format.

HSETPT
(timestamp, component,  
value, length,  
output_format)

Inserts the numeric value of a specified component into a date-time field.

HTIME
(length, timestamp,  
output_format)

Converts the time portion of a date-time field to a numeric number of milliseconds (if the first argument is 8) or microseconds (if the first argument is 10).

HTMTOTS
(time, length,  
output_format)

Converts a time to a timestamp.

Format Conversion Functions

EDIT
(fieldname)

Converts between alphanumeric and integer data. Used with a mask, extracts characters from an alphanumeric string and inserts new characters.

FTOA
(number, '(format)',  
output_format)

Converts numbers from decimal format to alphanumeric format.

ITONUM
(maxbytes, infield,  
output_format)

Converts large binary integers in non-FOCUS files to double-precision format.

ITOPACK
(maxbytes, infield,  
output_format)

Converts large binary integers in non-FOCUS files to packed-decimal format.

ITOZ
(length, in_value,  
output_format)

Converts numbers from numeric format to zoned format for extract files.

PTOA
(number, '(format)',  
output_format)

Converts a number from numeric format to alphanumeric format.

TSTOPACK
(timestamp'P2')

Converts an MS SQL Server or Sybase column described as "timestamp", which contains an increasing counter that represents a unique value in the database, in hex notation with a format of A16 into a packed decimal number with a format of P21.

Legacy Functions

ASIS
(argument)

Distinguishes between a space and a zero.

ATODBL
(source_string,  
length, output_format)

Converts a number in alphanumeric format to decimal format.

BAR
(barlength, infield, 
maxvalue, 'char', 
output_format)

Produces a bar chart.

BITSON
(bitnumber,  
source_string,  
output_format)

Evaluates an individual bit within a character string to determine whether it is on or off.

BITVAL
(source_string,  
startbit, number,  
output_format)

Evaluates a string of bits within character strings and returns its binary value.

BYTVAL
(character,  
output_format)

Translates a character to its corresponding ASCII code.

CHKPCK
(length, in_value,  
error, output_format)

Validates the data in a field described as packed format.

FIND
(fieldname [AS  
dbfield] IN file);

Verifies if a value exists in an indexed field in another file.

HEXBYT
(decimal_value,  
output)

Converts a numeric value to its corresponding ASCII character.

LOOKUP
field

Retrieves a data value from a cross-referenced FOCUS data source in a MODIFY request.

PCKOUT
(in_value, length,  
output_format)

Writes packed numbers of varying lengths (between one and 16 bytes) to extract files.

REVERSE
(length,  
source_string,  
output_format)

Reverses the input characters.

UFMT
(source_string,  
length, output_format)

Converts characters in alphanumeric field values to hexadecimal (HEX) representation.

Numeric Functions

ABS
(in_value)

Returns the absolute value of its argument.

DMOD
(dividend, divisor,  
output_format)

Returns the remainder from a division as a number in decimal format.

EXP
(power, output_format)

Raises the number e to a power you specify.

EXPN
(n.nn {E|D} {+|-} p)

Evaluates an argument expressed in scientific notation.

FMOD
(dividend, divisor,  
output_format)

Returns the remainder from a division as a number in floating-point format.

IMOD
(dividend, divisor,  
output_format)

Returns the remainder from a division as a number in integer format.

INT
(in_value)

Returns the integer part of its argument.

LOG
(in_value)

Returns the natural logarithm of its argument.

MAX
(value1, value2, ...)

Returns the maximum value from its list of arguments.

MIN
(value1, value2, ...)

Returns the minimum value from its list of arguments.

NORMSDST
(value, output_format)

Calculates the cumulative normal standard distribution.

NORMSINV
(value, output_format)

Calculates the inverse cumulative normal standard distribution.

PRDNOR
(seed, output_format)

Generates reproducible random numbers.

PRDUNI
(seed, output_format)

Generates reproducible random numbers.

RDNORM
(output_format)

Generates random numbers.

RDUNIF
(output_format)

Generates random numbers.

SQRT
(in_value)

Returns the square root of its argument.

System Functions

APINPATH
(length, appname, ‘A1’)

Returns Y if application appname is in the path, n if not.

CLSDDREC
(output_format)

Closes all files opened by the PUTDDREC function.

FEXERR
(error, 'A72')

Returns the first line of the error message.

FGETENV
(length, varname,  
outlen, output_format)

Gets the value of an environmental variable and returns it as an alphanumeric string.

FPUTENV
(varname_length,  
varname, value_length,  
value, output_format)

Assigns a character string to an environmental variable.

GETUSER
(output_format)

Retrieves the user ID from the system.

PUTDDREC
(ddname, dd_len,  
record_string,  
record_len,  
output_format)

Writes a character string as a record in a flat file.



x
Procedure: How to Use Function Assist

When you use a function in one of the calculators, the Assist dialog box can help you set the parameters.

  1. Click the Functions tab in one of the calculators.
  2. Right-click the Name heading bar to display the functions by Tree View or in List View.
  3. Double-click a function to add it to the Expression tab.

    The Function Assist dialog box opens.

    Function Assist Dialog Box

  4. Enter alphanumeric parameters in their fields.
  5. Enter parameters that require column names by selecting them from the drop-down menu, as seen in the following image.

    Function Assist Drop-Down Menu

    In addition to the column name, the format, description, and nullability are also displayed.

  6. When all the parameters in the Assist dialog box have been set, click OK.

    The function is added to the Expression tab of the calculator.

    SQL Calculator image

    Note: The Function Assist button enables you to edit a function in the Expression tab using the Function Assist dialog box.


iWay Software