HP NonStop Data Transformation Engine Functions and Expressions Reference Guide Abstract This document provides information about the functions, expressions, and reserved words that can be used to create component rules in the HP NonStop™ Data Transformation Engine (NonStop DTE) Type Designer and map rules in the NonStop DTE Map Designer. Product Version NonStop Data Transformation Engine 6.7.
Document History Part Number Product Version Published 427621-001 NonStop Data Transformation Engine 1.0 December 2000 522440-001 NonStop Data Transformation Engine 5.0.3 November 2001 522440-002 NonStop Data Transformation Engine 6.7.
Contents About This Document Related References........................................................................................... 8 Chapter 1 - Expressions and Evaluations Expressions..................................................................................................... 9 Component Rule Expressions Evaluate to True or False .......................................... 9 Map Rule Expressions Evaluate to Data............................................................. 10 Literals ......
Contents Numeric Precision ...................................................................................... 41 Chapter 2 - Using Functions Functions...................................................................................................... 42 Functions in a Component Rule ...................................................................... 42 Functions in a Map Rule ............................................................................... 42 Syntax of a Function ...................
Contents FILLLEFT..................................................................................................... FILLRIGHT ................................................................................................... FIND .......................................................................................................... FROMBASETEN .............................................................................................. FROMDATETIME....................................................
Contents ROUND ....................................................................................................... RUN........................................................................................................... SEARCHDOWN .............................................................................................. SEARCHUP................................................................................................... SERIESTOTEXT .......................................................
Contents Appendix A - Hex, Decimal, and Mercator Symbol Values Appendix B - Format Strings Date and Time Format Strings........................................................................... 171 Time Units ..............................................................................................171 Date Units...............................................................................................171 Binary Date and Time Format Strings ..........................................................
About This Document This Functions and Expressions Reference Guide contains information about the expressions, functions, and reserved words that can be used to create component rules in the Type Designer and map rules in the Map Designer. The Examples directory, located in your default installation directory, contains type trees and maps that can help you learn how to accomplish your mapping tasks.
Chapter 1 - Expressions and Evaluations This chapter explains how expressions are evaluated in component rules and map rules. Expressions An expression is a statement about data objects. Expressions are used in component rules in the Type Designer and in map rules in the Map Designer.
Chapter 1 - Expressions and Evaluations Literals Map Rule Expressions Evaluate to Data Map rules are expressions that evaluate to data. For example, the result of each of the following map rules is data: = "Florida" = Price:Input = COUNT ( Name:Roster ) = RecordMap ( FixedRecord ) Literals A literal is a constant value. A literal may be a number or a text string. The rules for using numeric literals are: ♦ A numeric literal is in integer or decimal format and can be signed.
Chapter 1 - Expressions and Evaluations Data Object Names Data Object Names A data object is referenced in an expression by its name. An object name can be as simple as a card name or a local type name. It can also be complex depending on the object you want to reference. Object Names in Map Rules In a map rule, data object names always refer to the card name that contains the data object name. So, in a map rule, a data object name ends with a card name.
Chapter 1 - Expressions and Evaluations Data Object Names The general definition of an object name in a map rule can be divided into three parts as described in the following table. If one of these parts is used, it must be sequenced according to the order in the table.
Chapter 1 - Expressions and Evaluations Data Object Names Object Names in Component Rules In a component rule, data object names always refer to components in the same component list. In a component rule, a data object name ends with a component name. For example, if LineItem is in a component list, the object name for the Qty Info component of LineItem is Qty Info:LineItem. Note In the preceding Qty Info:LineItem example, the colon would be interpreted as "is a component of".
Chapter 1 - Expressions and Evaluations Data Object Names Local Type Name An object name can be a local type name. A local type name is specified as one or more simple type names separated by spaces, such as: City Field Local type names are typically used to refer to a component. If used in a component rule, for example, the component referenced in a rule is specified as a local type name. In a map rule, local type names refer to components contained in a card object.
Chapter 1 - Expressions and Evaluations Data Object Names Ball Room[LAST] Really<>Grand<>Ball Room Ball Room Indexed Object Names An object name can refer to a particular occurrence of a data object. The index of the occurrence appears in square brackets immediately after its name.
Chapter 1 - Expressions and Evaluations Data Object Names LAST refers to the previous occurrence of Order because, when this rule is evaluated, the last known Order is the last one built. Note LAST is a reserved word and is not case-sensitive. Component Paths Separated by a Colon A component in the component list of another object is always referenced by a colon :. The local type name of the nested component is followed by the colon.
Chapter 1 - Expressions and Evaluations Data Object Names The object name for all occurrences of ShipSchedules within ORD_ADR_OUT_WD Record is: ShipSchedules IN ORD_ADR_OUT_WD Record:OrderAck Note When IN is used, the contained object name must refer to the same type. In the example, the ShipSchedules component of ORD_ADR_OUT_WD Record and the ShipSchedules component of OrderLine Structure must be of the same type.
Chapter 1 - Expressions and Evaluations Data Object Names The IN COMPONENT expression can be used: #Topic Field:$ = COUNT (Topic IN COMPONENT) Note The above component rule works only if Topic is partitioned.
Chapter 1 - Expressions and Evaluations Data Object Names Comment Object Name A comment name can be either an in-comment name or an @-comment name. ♦ An in-comment name references all comment objects within a specified object. An in-comment name is specified as a component-path, a space, the reserved word IN, followed by another space, followed by the component path of the component on which the floating component type is defined.
Chapter 1 - Expressions and Evaluations Data Object Names Using the Dollar Sign ($) The dollar sign can be used to refer to the object to the left of a rule cell. For example, the component rule below applies to the component RunRule. The RunRule in the rule can be replaced by $. You can remember it this way—a dollar sign always means "whatever component appears to the left of the rule".
Chapter 1 - Expressions and Evaluations Evaluating Expressions Using Ellipses A single period can be used, like ellipses, in place of object names, as long as that object name can be interpreted as a unique object. For example, if you have the following object name: City Field:BillTo Customer:Account:File you may be able to replace it with City Field:.
Chapter 1 - Expressions and Evaluations Evaluating Expressions For example, suppose you want to evaluate the following map rule. NumberSet ( s ) = B:Card2 + A:Card1 In this example, an evaluation set for this rule consists of one value for B and one value for A. One evaluation set produces one NumberSet. As an example, here there are three As and three Bs in the data: A Values B Values 1 3 2 2 3 1 There will be nine evaluation sets.
Chapter 1 - Expressions and Evaluations Evaluating Expressions Case 2: If Card 2 is the first card, you will get the following result: Evaluation # B A Result 1 3 1 4 2 2 1 3 3 1 1 2 4 3 2 5 5 2 2 4 6 1 2 3 7 3 3 6 8 2 3 5 9 1 3 4 If both A and B are contained in the same card object, evaluation sets are selected based on the order A and B appear in the rule. Leftmost objects are selected first.
Chapter 1 - Expressions and Evaluations Evaluating Expressions NumberSet[1] = 7 This is Evaluation Set 2: A Values B Values 1 3 2 2 3 1 NumberSet[2] = 8 This is Evaluation Set 3: A Values B Values 1 3 2 2 3 1 NumberSet[2] = 9 If your rule looks like NumberSet ( s ) = SUM ( A:Card1 ) + SUM ( B:Card1 ) there will be only one evaluation set: A Values B Values 1 3 2 2 3 1 NumberSet[1] = 12 If you have the following rule: NumberSet ( s ) = A:Card1 + EXTRACT ( B:Card2 , B:Card2 != 2 ) the or
Chapter 1 - Expressions and Evaluations Evaluating Expressions are determined for that A and a given EXTRACT result. When all EXTRACT results are used, the next A is selected, and so on.
Chapter 1 - Expressions and Evaluations Evaluating Expressions Object Names Influence the Number of Evaluation Sets In some expressions, the same data object name may appear more than once. The same data object name always refers to the same data object. The same data object name influences the combinations of evaluation sets that are selected.
Chapter 1 - Expressions and Evaluations Evaluating Expressions Using IN to De-couple Object Name Coordination Suppose you have a situation in which you do not want to coordinate common objects. You may, for example, want to count different objects contained in the same object, as in: Summary = COUNTABS ( Header:Order:Message ) + COUNTABS ( Line Item:Order:Message ) If there are multiple Orders in your data, the Summary would not be evaluated correctly.
Chapter 1 - Expressions and Evaluations Operators Operators Operators are used for arithmetic functions and text functions on items. If an operator requires two operands, the operator symbol appears between them. For example, in the expression a + b, the operator symbol "+" appears between the operands a and b. Operators are reserved symbols that cannot be used in type names.
Chapter 1 - Expressions and Evaluations Operators Logical Operators Logical operators are used in expressions that evaluate to TRUE or FALSE.
Chapter 1 - Expressions and Evaluations Operators Order of Operator Evaluation Use parentheses to group operations. Expressions within parentheses are evaluated before performing other operations. For example: (UnitPrice – Discount) * Tax The expression UnitPrice - Discount is evaluated before the rest of the expression. Some operators are evaluated before others according to standard rules of precedence.
Chapter 1 - Expressions and Evaluations Using Functions in Expressions Operands Operands are the arguments for an operator. For example, in the expression a + b, the + is the operator symbol, and a and b are the operands. An operand can be any of the following: ♦ a literal ♦ a data object name ♦ a function ♦ another operator Using Functions in Expressions Functions perform a particular action on its input arguments. A function is written like this: FUNCTION (argument1, argument2, ...
Chapter 1 - Expressions and Evaluations Using Functions in Expressions argument it is used for. The analysis also checks the type of the output argument when that function is used to directly produce an output data object. The output argument of a function specifies the result that the function produces for one evaluation. For example, one evaluation of the ABS function produces one positive number. One function evaluation may produce multiple outputs.
Chapter 1 - Expressions and Evaluations Using Functions in Expressions Nested Input Arguments When a function, operator, or map is used as an input argument, each of these has arguments of its own. Arguments of other arguments are said to be nested. For example, in the use of the ABS function, you can use a function as an argument: ABS ( UNIQUE ( Quantity:LineItem ) ) The output of the UNIQUE function becomes the input argument to the ABS function.
Chapter 1 - Expressions and Evaluations Map Names in Expressions Some functions use an entire series of objects as the value of an argument for one evaluation. For example, the COUNT function uses an entire series of data objects as the value of its argument for one evaluation. COUNT ( LineItem:PO ) When a series of data objects is used for one evaluation, the function evaluates just once when there is more than one data object that fits the argument definition.
Chapter 1 - Expressions and Evaluations Map Names in Expressions Note Unlike a function, a map cannot be used as an argument to an operator or another map. A map may be used as an argument to a function only if the output of the map evaluates directly to output data objects.
Chapter 1 - Expressions and Evaluations Expressions That Evaluate to NONE Expressions That Evaluate to NONE The following discussion explains how the Map Designer evaluates an expression when expressions within it evaluate to NONE. When an Operand Evaluates to NONE The following table explains how an operator expression evaluates when an operand evaluates to NONE. The symbol A represents one operand of the expression and B represents the other operand. The symbol != means not equal to.
Chapter 1 - Expressions and Evaluations Expressions That Evaluate to NONE Operator Expression Condition Expression Evaluates to A>B A != NONE B = NONE TRUE A
Chapter 1 - Expressions and Evaluations Expressions That Evaluate to NONE If you want OrderMap to be evaluated even when SummaryRecord is missing, use the entire Order as an input argument, rather than SummaryRecord. This will ensure that OrderMap will be evaluated, even if SummaryRecord is missing. Then, in the map OrderMap, you can still map from SummaryRecord.
Chapter 1 - Expressions and Evaluations Evaluated Expressions Assigned to Output Items Note Track Places will only have an impact on the sequence of the output when both the input group object and output group object are defined as Track Places. However, Track Places will have an impact in situations in which an input defined with Track Places is referenced by index.
Chapter 1 - Expressions and Evaluations Automatic Item Format Conversions ♦ Missing part of an output date format. If a portion of the format of an output date cannot be derived from the input date, the missing part will have pound characters (#) in its place. For example, if 09/98 is the date for an input with a format string of MM/YY and that date was mapped to an output date with a format of MM/DD/YY, the output date would be built as 09/##/98. ♦ Missing part of an output time format.
Chapter 2 - Using Functions This chapter explains functions used in component rules and map rules. For additional examples of using functions, see the Introduction to the Design Studio. Functions A function is an expression that generates an output by performing a certain operation on one or more inputs. Most functions can be used in both component rules and map rules. Those that produce a series can only be used in map rules.
Chapter 2 - Using Functions Function Argument Syntax object name. Depending on the function, one or many data objects can be used for an input argument for one function evaluation.
Chapter 2 - Using Functions List of Functions by Category Term Explanation object-name The name of a data object, as defined in Chapter 1, Data Object Names. If an object name is further limited, it is specified as a prefix. For example, simple-object-name refers to a simple type name. ... Indicates that the function can take on any number of additional similar arguments. [] Indicate that the argument is optional. { literal , literal ...} An enumerated series of literals.
Chapter 2 - Using Functions List of Functions by Category Function Description COUNTABS Counts the number of objects, valid and invalid, in a series FROMBASETEN Converts an integer to a text item that can be interpreted as a number using positional notation of the base specified INT Returns the integer portion of a number MAX Returns the maximum value from a series of number, date, time, or text values MIN Returns the minimum value from a series of number, date, time, or text values MOD Retur
Chapter 2 - Using Functions List of Functions by Category Date/Time Functions Function Description ADDDAYS Adds a specified number of days to a date ADDHOURS Returns a time value that is the result of adding a specified number of hours to a given time ADDMINUTES Returns a time value that is the result of adding a specified number of minutes to a given time CURRENTDATE Returns the system date CURRENTDATETIME Returns the system date and time CURRENTTIME Returns the system time DATETONUMBER Retu
Chapter 2 - Using Functions List of Functions by Category Conversion Functions Function Description BCDTOHEX Converts an item from BCD format to hex format with the high-order byte first, regardless of platform BCDTOINT Converts an item from BCD format to integer format.
Chapter 2 - Using Functions Function List of Functions by Category Description item STREAMTOHEXTEXT Returns a character text stream represented by hex pairs whose value is the evaluation of input binary text SYMBOL Returns a character that is the ASCII character equivalent of a specified decimal value TEXTTOBCD Converts a text item from decimal digits to binary coded decimal (BCD) format TEXTTODATE Converts text that is in CCYYMMDD or YYMMDD presentation to a date that looks like the text item TE
Chapter 2 - Using Functions List of Functions by Category Logical Functions Function Description ALL Evaluates a series of conditions and returns TRUE if they all evaluate to TRUE; otherwise returns FALSE EITHER Returns the result of the first argument that does not evaluate to NONE IF Evaluates a conditional expression, returning one value if true, another if false NOT Tests a condition, returning TRUE if the condition is not true, FALSE if the condition is true OR Evaluates a series of condit
Chapter 2 - Using Functions Function List of Functions by Category Description HEXTEXTTOSTREAM Returns a binary text stream whose value is the evaluation of input character text represented by hex pairs ISALPHA Tests a text object to see whether it is contains all alphabetic characters ISLOWER Tests a text object to see whether it is contains all lowercase, alphabetic characters ISNUMBER Tests a text object to see whether it is a number ISUPPER Tests a text object to see whether it is contains a
Chapter 2 - Using Functions List of Functions by Category Function Description PROPERCASE Converts each "word" in a string, such that the first alphabetic character is uppercase and the remaining alphabetic characters are lowercase REVERSEBYTE Reverses the byte order of an item RIGHT Extracts a specified number of characters of a text item, starting with the rightmost character SERIESTOTEXT Converts a contiguous or non-contiguous series to a text item SIZE Returns, as an integer, the size of an
Chapter 2 - Using Functions List of Functions by Category Function Description TRIMRIGHT Removes trailing spaces (or another specified character) from a text item UPPERCASE Converts text to uppercase characters WORD Returns the characters between two user-defined separators within a delimited text item Functions and Exp ressions Referen ce Guide 51
Chapter 2 - Using Functions List of Functions by Category Lookup and Reference Functions Function Description CHOOSE Returns the object within a series whose position (index) in the series corresponds to a specified number DBLOOKUP Executes an SQL statement against a database, returning the results excluding the final column delimiter and row terminator Note Only available when used with a corresponding database adapter.
Chapter 2 - Using Functions List of Functions by Category Function Description INDEXABS Returns an integer that represents the index of an object relative to its nearest contained object, counting both valid and invalid instances of the object LASTERRORCODE Returns a text item whose value is the last error code returned by one of a specified set of functions during map execution LASTERRORMSG Returns a text item whose value is the message corresponding to the last error code returned by one of a spe
Chapter 2 - Using Functions List of Functions by Category Function Description EXIT Allows interfacing to a function in an external library or application GET Returns data from a source adapter.
Chapter 2 - Using Functions List of Functions by Category Inspection Functions Function Description ABSENT Tests for the absence of an object CONTAINSERRORS Tests a valid object to see whether it contains any objects in error ISALPHA Tests a text object to see whether it contains all alphabetic characters ISERROR Tests an object to see whether it is in error ISLOWER Tests a text object to see whether it is all lowercase alphabetic characters ISNUMBER Tests a text object to see whether it is a
Chapter 2 - Using Functions List of Functions by Category General Functions Function Description CLONE Creates a specified number of copies of an object GETANDSET Gets a fixed length value from the input data stream, updates that value in the input data stream, and returns either the original or updated value GETDIRECTORY Returns the full path (directory) for the compiled map or the source or destination associated with a specified card object GETFILENAME Returns the file name of a file source or
Chapter 3 - Function Reference The following pages provide detailed information and examples of the functions that can be used in component rules and map rules. For hands-on examples of using functions, see the Design Studio Tutorial. Note Quotation mark usage information can be found in Introduction to the Design Studio. ABS The ABS function returns the absolute value of a number. Syntax ABS (single-number-expression) Meaning ABS (number) Returns A single-number.
Chapter 3 - Function Reference ABS Uses Use ABS when you need the absolute value of a number. See the section Numeric Precision for more information.
Chapter 3 - Function Reference ABSENT ABSENT The ABSENT function tests for the absence of an object. Syntax ABSENT (single-object-expression) Meaning ABSENT (object_to_test) Returns This function returns TRUE or FALSE. Returns TRUE if the object_to_test evaluates to NONE. Returns FALSE if the object_to_test does not evaluate to NONE. Example ABSENT (AreaCode:Phone) This example evaluates to TRUE if AreaCode:Phone evaluates to NONE and evaluates to FALSE if AreaCode:Phone does not evaluate to NONE.
Chapter 3 - Function Reference ADDDAYS ADDDAYS The ADDDAYS function adds a specified number of days to a given date. Syntax ADDDAYS (single-date-expression, single-integer-expression) Meaning ADDDAYS (any_date, number_of_days_to_add) Returns This function returns a single-date. Returns the date that results from adding number_of_days_to_add to any_date.
Chapter 3 - Function Reference ADDDAYS Uses You may need to increment a date by a fixed number of days, such as to calculate a DueDate that is always 30 days after the InvoiceDate. To produce a date in the output, such as a ship date, you may need to add a variable number of days to a date in the input; for example, ADDDAYS (PODate, LeadTime).
Chapter 3 - Function Reference ADDHOURS ADDHOURS The ADDHOURS function returns a time value that is the result of adding a specified number of hours to a given time. Syntax ADDHOURS (single-datetime-item-expression, hours-expressedas-signed-integer-expression) Meaning ADDHOURS (any_datetime, number_of_hours_to_add) Returns This function returns a single-datetime item.
Chapter 3 - Function Reference ADDHOURS Related Functions ADDDAYS, DATETONUMBER, NUMBERTODATE, TEXTTODATE, TODATETIME Functions and Exp ressions Referen ce Guide 63
Chapter 3 - Function Reference ADDMINUTES ADDMINUTES The ADDMINUTES function returns a time value that is the result of adding a specified number of minutes to a given time. Syntax ADDMINUTES (single-datetime-item-expression, minutesexpressed-as-signed-integer-expression) Meaning ADDMINUTES (any_datetime, number_of_minutes_to_add) Returns This function returns a single-datetime item.
Chapter 3 - Function Reference ALL ALL The ALL function evaluates a series of conditions and returns TRUE if they all evaluate to TRUE; otherwise returns FALSE Syntax ALL (series-condition-expression) Meaning ALL (conditions_to_evaluate) Returns This function returns TRUE or FALSE. Evaluates to TRUE if all members of the input argument evaluate to TRUE. Evaluates to FALSE if any member of the input argument is FALSE.
Chapter 3 - Function Reference BCDTOHEX BCDTOHEX The BCDTOHEX function converts an item from BCD (binary coded decimal) format to hex (hexadecimal) format. The binary value that is returned always has the high-order byte first, regardless of the platform. Syntax BCDTOHEX (single-text-expression, single-integer-expression) Meaning BCDTOHEX (BCD_item_to_convert, length_of_output) Returns This function returns a single-binary-bytestream. The input argument is converted from BCD format to its binary value.
Chapter 3 - Function Reference BCDTOHEX Uses Use BCDTOHEX to test a specific bit position for a specific value. For example, BCDTOHEX is useful when the BCD value represents flag bits. The conversion performed by BCDTOHEX results in a predictable location for the flag bits across platforms where the bytes may otherwise be reversed.
Chapter 3 - Function Reference BCDTOINT BCDTOINT The BCDTOINT function converts an item from BCD (binary coded decimal) format to integer format. The binary value that is returned has a form native to the machine on which the map is being run. Syntax BCDTOINT (single-text-expression , single-integer-expression) Meaning BCDTOINT (BCD_item_to_convert , length_of_output) Returns This function returns a single-binary-number.
Chapter 3 - Function Reference BCDTOINT Uses Use BCDTOINT to use a BCD number in arithmetic operations or to compare to numeric values. It should not be used for bit manipulations, because the order of the bytes in the number depends on the platform.
Chapter 3 - Function Reference BCDTOTEXT BCDTOTEXT The BCDTOTEXT function converts the digits in a BCD (Binary Coded Decimal) item to a text item containing the digits of the BCD-encoded item as a string of characters. Syntax BCDTOTEXT (single-text-expression) Meaning BCDTOTEXT (BCD_item_to_convert) Returns This function returns a single-text-item. BCD_item_to_convert is converted from BCD format to a text string containing the digits of the BCD-encoded value as a string of characters.
Chapter 3 - Function Reference BCDTOTEXT Uses Use BCDTOTEXT to convert a BCD value to a text value.
Chapter 3 - Function Reference CHOOSE CHOOSE The CHOOSE function returns the object within a series whose position in the series corresponds to a specified number. Syntax CHOOSE (series-object-name , single-integer-expression) Meaning CHOOSE (from_these_objects , pick_the_nth_one) Returns This function returns a single-object Produces a single-object whose index within the from_these_objects series matches the number specified by pick_the_nth_one.
Chapter 3 - Function Reference CLONE CLONE The CLONE function creates a specified number of copies of some object. Syntax CLONE (single-object-name , single-integer-expression ) Meaning CLONE (object_to_copy , number_of_copies) Returns This function returns a series-object. The CLONE function returns a series of the object specified by object_to_copy. The output series consists of as many copies of the object as specified by number_of_copies.
Chapter 3 - Function Reference CONTAINSERRORS CONTAINSERRORS The CONTAINSERRORS function tests a valid object to see whether it contains any objects in error. Syntax CONTAINSERRORS (single-object-expression) Meaning CONTAINSERRORS (object_to_test) Returns This function returns TRUE or FALSE Returns TRUE if any object contained in object_to_test is in error Returns FALSE if the object_to_test is completely valid Note The input object, itself, is a valid input object.
Chapter 3 - Function Reference CONTAINSERRORS Uses Use CONTAINSERRORS when you conditionally want to accept or reject an input object if it contains errors. For example, you may want to produce a particular file or output data, if errors are detected in an input. In another situation, you may want to only produce an output file if there are no errors in the input.
Chapter 3 - Function Reference CONVERT CONVERT The CONVERT function replaces each byte of a byte stream or text expression with a byte from another byte stream or text expression. The decimal value of each byte of the first argument is used to locate the corresponding byte in the second argument that will replace it.
Chapter 3 - Function Reference COUNT COUNT The COUNT function returns an integer representing the number of valid input objects in a series. Syntax COUNT (series-object-expression) Meaning COUNT (valid_objects_to_count) Returns This function returns a single-integer. The result is the number of valid_objects_to_count. If the input argument evaluates to NONE, COUNT returns 0. Note COUNT does not count existing NONEs unless its group was defined as an explicit format with a Track setting of Places.
Chapter 3 - Function Reference COUNTABS COUNTABS The COUNTABS function returns an integer representing the number of input objects in a series. Unlike COUNT, COUNTABS includes both valid and invalid objects in a series. Syntax COUNTABS (series-object-expression) Meaning COUNTABS (objects_to_count) Returns This function returns a single-integer. The result is the number of objects_to_count. If the input argument evaluates to NONE, COUNTABS returns 0.
Chapter 3 - Function Reference COUNTSTRING COUNTSTRING The COUNTSTRING function returns an integer representing the number of times that a specified character string appears in another character string. The function begins to look for the character string from the first position of the first string, and proceeds forward one byte at a time.
Chapter 3 - Function Reference CURRENTDATE CURRENTDATE The CURRENTDATE function returns the current system date. Syntax CURRENTDATE ( ) Note Although CURRENTDATE has no arguments, it does require parentheses. Meaning CURRENTDATE ( ) Returns This function returns a single-date. Returns the system date. If being assigned to a date/time output item, the current date is returned in the format specified by that output item. Otherwise, the system date is returned in an MM/DD/YY presentation.
Chapter 3 - Function Reference CURRENTDATETIME CURRENTDATETIME The CURRENTDATETIME function returns the current system date and time. Syntax CURRENTDATETIME ( [ single-text-expression ] ) Note Even when using CURRENTDATETIME without an argument, it does require parentheses. Meaning CURRENTDATETIME ( [ date_time_format_string ] ) Returns This function returns a single-date-time.
Chapter 3 - Function Reference CURRENTDATETIME Uses Use CURRENTDATETIME when you need to map the current system date and time to an item that includes both a date and time portion.
Chapter 3 - Function Reference CURRENTTIME CURRENTTIME The CURRENTTIME function returns the current system time. Syntax CURRENTTIME ( ) Note Although CURRENTTIME has no arguments, it does require parentheses. Meaning CURRENTTIME ( ) Returns This function returns a single-time. Returns the system time. If assigned to a date/time output item, the current time is returned in the format specified by that output item. Otherwise, the system time is returned in HH:MM:SS presentation.
Chapter 3 - Function Reference DATETONUMBER DATETONUMBER The DATETONUMBER function returns an integer that results from counting the number of days from December 31, 1864, to the specified date. Syntax DATETONUMBER (single-date-expression) Meaning DATETONUMBER (date_to_convert) Returns This function returns a single-integer. Converts a date to an integer.
Chapter 3 - Function Reference DATETOTEXT DATETOTEXT The DATETOTEXT function converts a date object or expression to a text item. Syntax DATETOTEXT (single-date-expression) Meaning DATETOTEXT (date_to_convert) Returns This function returns a single-text-item. If date_to_convert is a date object name, this returns the date as a text item formatted according to the presentation of the date object.
Chapter 3 - Function Reference DBLOOKUP DBLOOKUP The DBLOOKUP function executes an SQL statement against a database. The SQL statement can be any permitted by your database management system or ODBC driver. Syntax DBLOOKUP ( single-text-expression , single-text-expression , [ single-text-literal ] ) Note There are two different ways to specify the arguments for DBLOOKUP.
Chapter 3 - Function Reference DBLOOKUP ! The first argument is an SQL statement as a text string. This can be any valid SQL statement permitted by your database management system and supported by your database-specific driver. In addition to a fixed SQL statement, this argument can be a concatenation of text literals and data objects, enabling the concatenation of data values into your SQL statement.
Chapter 3 - Function Reference DBLOOKUP Returns This function returns a single-text-item. The DBLOOKUP function returns the results of the query in the same format as a query specified for a map input card, except that it does not include the last carriage return/linefeed. Because this information is removed, it is easier to make use of a single value extracted from a database.
Chapter 3 - Function Reference DBLOOKUP Uses Use DBLOOKUP to execute an SQL statement when you want to execute a SELECT statement to retrieve a specific column value in a large table in a database using the value of another input, rather than defining the entire table as an input card and using the LOOKUP, SEARCHDOWN, or SEARCHUP functions.
Chapter 3 - Function Reference DBQUERY DBQUERY The DBQUERY function executes an SQL statement against a database. The SQL statement can be any permitted by your database management system or ODBC driver. Syntax DBQUERY (single-text-expression , single-text-expression , [ single-text-literal ] ) Note There are two different ways to specify the arguments for DBQUERY.
Chapter 3 - Function Reference DBQUERY Meaning 2 DBQUERY ( SQL_statement , parameters ) The arguments for DBQUERY are: ! The first argument is an SQL statement as a text string. This can be any valid SQL statement that is permitted by your database management system and supported by your database-specific driver. In addition to a fixed SQL statement, this argument can be a concatenation of text literals and data objects, enabling the concatenation of data values into your SQL statement.
Chapter 3 - Function Reference ! DBQUERY The order of the keywords is not important. All database-specific parameters are optional. Returns This function returns a single-text-item. If your SQL statement is a SELECT statement, this function returns the results of the query in the same format as a query specified as a map input card, including row delimiters and terminators, and so on. If your SQL statement is anything other than a SELECT statement, this function returns NONE.
Chapter 3 - Function Reference DBQUERY In another example, assume that you have an input file containing one order record. To map that order to another proprietary format, you also have a parts table with pricing information for every part for every customer, a very large table.
Chapter 3 - Function Reference DDEQUERY DDEQUERY The DDEQUERY function allows you to interface to other Windows applications such as Trading Partner PC, Excel, and so forth, provided that certain criteria are met. For example, if you receive an Excel spreadsheet file, you must have the appropriate version of the Excel application installed (that is compatible with the file received) and the application must be open.
Chapter 3 - Function Reference DDEQUERY Uses As shown in the examples above, use DDEQUERY to retrieve data from an Excel spreadsheet. Also, you can use DDEQUERY to ask Trading Partner PC for sender or receiver IDs for particular trading partners.
Chapter 3 - Function Reference DEFAULT DEFAULT The DEFAULT function allows a pre-defined value to be introduced into an expression. DEFAULT returns the value assigned as the default value when the type was defined. Typically used with the EITHER function. Syntax DEFAULT(single-type-expression) Meaning DEFAULT(type_whose_defined_default_value_is_desired) This function returns a single-object. Example In this example, the defined default value for ZipCode is returned.
Chapter 3 - Function Reference ECHOIN ECHOIN The ECHOIN function returns a command (or property) to be used in a RUN function argument. Syntax ECHOIN (single-integer-expression, single-text-expression) Meaning ECHOIN (card#, item_or_group_interpreted_as_text) Returns This function returns a single-object.
Chapter 3 - Function Reference EITHER EITHER The EITHER function returns the result of the first argument that does not evaluate to NONE. Syntax EITHER (single-general-expression { , single-generalexpression } ) Meaning EITHER ( try_this { , if_none_try_this } ) Returns This function returns a single-object. Returns try_this if try_this does not evaluate to NONE. Returns if_none_try_this if try_this evaluates to NONE.
Chapter 3 - Function Reference EITHER of Reference#. If none of those conditions result in a value, EITHER returns "####". Uses Use EITHER when you want a default when an expression evaluates to NONE and the expression may cause common arguments to produce an unintended result.
Chapter 3 - Function Reference EXIT EXIT The EXIT function allows you to interface with a function in an external library or application. Depending on the execution platform, there are two different methods for the EXIT function.
Chapter 3 - Function Reference EXIT unsigned int i; char *syms[] = { "bin2hex", "AIXEntry" }; void *adr[] = { (void *)bin2hex, (void *)AIXEntry}; void AIXEntry(LPEXITPARAM lpInputStruct) { printf("AIXEntry called\n"); for (i = 0; i < (sizeof(syms)) / (sizeof(char *)); i++) if ( !(strcmp(lpInputStruct->szFile, syms[i])) ) { lpInputStruct->lpv = adr[i]; break; } } void bin2hex(LPEXITPARAM lpInputStruct) { printf("bin2hex called\n"); printf("argument to library function: %s\n",lpInputStruct>lpszCmdLine); lpI
Chapter 3 - Function Reference EXIT At execution time, the program specified by program_name executes and passes the concatenation of command_line_arg1 + " " + command_line_arg2 as a text string. Whatever is returned by program_name to the standard output device is returned as text. Returns This function returns a single-text-item. Returns a text string from the function or application that is executed. If the EXIT function is not available for a particular platform, EXIT returns NONE.
Chapter 3 - Function Reference EXIT Uses Use EXIT when you need information from an existing function in a library or a program or when you need to use a general function that is not available.
Chapter 3 - Function Reference EXTRACT EXTRACT The EXTRACT function returns all members of a series for which a specified condition is true. Syntax EXTRACT (series-object-expression, single-conditionexpression) Meaning EXTRACT (objects_to_extract, condition_to_evaluate) Returns This function returns a series-object. The result is each member of series_to_search for which the condition specified by condition_to_evaluate evaluates to TRUE.
Chapter 3 - Function Reference FAIL FAIL The FAIL function returns NONE, aborts the map and returns a text string to be reported as the map completion error message. Syntax FAIL (single-text-expression) Meaning FAIL (message_to_return) Returns This function returns NONE. Returns NONE to the output to which the function is assigned, aborts the map, and returns message_to_return as the map completion error message included in the execution audit.
Chapter 3 - Function Reference FAIL Message = VALID (RUN ("Map1Msg.mmc", "-AE –OMMSMQ1B ‘-QN .\aqueue -CID 2001’" ), FAIL ("Failure on RUN (" + TEXT (LASTERRORCODE ( ) ) + "):" + LASTERRORMSG ( ) ) ) In this example, the FAIL function is being used in conjunction with the VALID, LASTERRORCODE, and LASTERRORMSG functions to fail (abort) the map if the map executed by the RUN function (Map1Msg.mmc) fails.
Chapter 3 - Function Reference FILLLEFT FILLLEFT The FILLLEFT function returns a text item of the length specified. The output is filled on its left with the specified pad value. Syntax FILLLEFT (single-text-expression , single-text-expression , single-integer-expression ) Meaning FILLLEFT (text_to_fill , pad_character , pad_to_length) Returns This function returns a single-text-item.
Chapter 3 - Function Reference FILLRIGHT FILLRIGHT The FILLRIGHT function returns a text item of the length specified. The output is filled on its right with the specified pad value. Syntax FILLRIGHT (single-text-expression , single-text-expression , single-integer-expression) Meaning FILLRIGHT (text_to_fill , pad_character , pad_to_length) Returns This function returns a single-text-item.
Chapter 3 - Function Reference FIND FIND The FIND function looks for one text string within another text string and returns to its starting position, if found. Syntax FIND (single-text-expression , single-text-expression [ , single-number-expression ] ) Meaning FIND (text_to_find, where_to_look[ , position_to_start_the_search ] ) Returns This function returns a single-integer. FIND returns the starting position of the text item specified by text_to_find within the text item specified by where_to_look.
Chapter 3 - Function Reference FIND Uses Use FIND when you need to know the location of a text string within another text string.
Chapter 3 - Function Reference FROMBASETEN FROMBASETEN The FROMBASETEN function converts an integer to a text item that can be interpreted as a number, using positional notation of the base specified. Syntax FROMBASETEN (single-integer-expression , single-integerexpression) Meaning FROMBASETEN (positive_integer_to_convert , base_to_convert_to) Returns This function returns a single-text-item.
Chapter 3 - Function Reference FROMDATETIME FROMDATETIME The FROMDATETIME function converts a date/time item to a text string of a specified format. Syntax FROMDATETIME (single-character-date-time-item [ , single-text-expression ] ) Meaning FROMDATETIME (date_time [ , date_time_format_string ] ) Returns This function returns a single-character_text_item. Returns the date/time specified by date_time as a text item with the format specified by date_time_format_string.
Chapter 3 - Function Reference FROMNUMBER Uses Use FROMDATETIME to convert a date/time item into a string for parsing or concatenation. Also, use FROMDATETIME when you want to access the individual parts of a date or time, such as the month, day, year, and so on. Related Functions CURRENTDATE, CURRENTDATETIME, CURRENTTIME, DATETOTEXT, TODATETIME FROMNUMBER The FROMNUMBER function converts a number to a text string of a specified format.
Chapter 3 - Function Reference GET If the value of CurrentRate Field is 15.875, this function will return "$15.88". Uses Use FROMNUMBER when you need to convert a number item into a string for parsing or concatenation. Related Functions DATETONUMBER, NUMBERTODATE, NUMBERTOTEXT, TONUMBER GET The GET function returns data from a source adapter. GET can also be used for adapters that support request/reply, such as Roma.
Chapter 3 - Function Reference GET If a source adapter error occurs, GET returns NONE. Use the VALID function with the GET function to identify whether the GET function executed successfully and to react accordingly. Examples Reply (s) = GET ( "ROMA", "-BE Orders22A -MID 32001 -REQ 2", PACKAGE (Request Object:.:Input) In this example, the GET function could be used in a request/reply mode to get information from a Roma messaging system.
Chapter 3 - Function Reference GETANDSET GETANDSET The GETANDSET function gets a fixed length value from the input data stream, updates that value in the input data stream, and returns either the original or updated value.
Chapter 3 - Function Reference GETANDSET Note This function does not support decrementing integers past 0 into negative numbers. Example New Tracking# = GETANDSET (Tracking#:Card, Tracking#:Card + 3, 1) This rule finds the object Tracking#:Card (assume that it has the value 6), adds 3 to it, giving 9, and replaces the 6 with the 9 in the data location of Tracking#:Card. Subsequent references to Tracking#:Card will find its value to be 9.
Chapter 3 - Function Reference GETDIRECTORY GETDIRECTORY The GETDIRECTORY function returns the full path (directory) for the compiled map file or the source or destination associated with a specified card object. Syntax GETDIRECTORY ([ single-simple-object-name ] ) Meaning GETDIRECTORY ([ card_or_object_for_which_directory_is_needed ]) Returns This function returns a single-text-item. Without an argument, GETDIRECTORY returns the full path associated with the compiled map.
Chapter 3 - Function Reference GETDIRECTORY Suppose you want to run the map, MyMap, from a component rule on Record in your input to determine whether input customer names are valid. MyMap has two inputs and one output. The first input is the customer name to up. The second input is a lookup file. The output is a text item whose value is VALID or ERROR. The name of the lookup file is constant; it is always XREF_TBL.TXT.
Chapter 3 - Function Reference GETFILENAME GETFILENAME The GETFILENAME function returns the file name for a file adapter source or target of a specified card object. Without an argument, it returns the adapter command associated with the active source or destination. Syntax GETFILENAME ( [ single-simple-object-name ] ) Meaning GETFILENAME ( [ card_for_which_resource_info_is_needed ] ) Returns This function returns a single-text-item.
Chapter 3 - Function Reference GETFILENAME Uses Use GETFILENAME in a map rule or component rule when you need the data file name.
Chapter 3 - Function Reference GETPARTITIONNAME GETPARTITIONNAME The GETPARTITIONNAME function returns a text item whose value is the name of the partition to which the data belongs. Syntax GETPARTITIONNAME (single-partitioned-object-expression) Meaning GETPARTITIONNAME (partitioned_object) Returns This function returns a single-simple-object-name. Returns a text item that represents the name of the partition to which the data for partitioned_object belongs.
Chapter 3 - Function Reference GETRESOURCENAME GETRESOURCENAME The GETRESOURCENAME function returns the adapter source or target command of a specified card object. Without an argument, it returns the adapter command associated with the active source or destination. Syntax GETRESOURCENAME ([ single-simple-object-name ]) Meaning GETRESOURCENAME ([ card_for_which_resource_info_is_needed ]) Returns This function returns a single-text-item.
Chapter 3 - Function Reference HEXTEXTTOSTREAM HEXTEXTTOSTREAM The HEXTEXTTOSTREAM function returns a binary text stream whose value is the evaluation of input character text represented by hex pairs. The HEXTEXTTOSTREAM function is the reverse of the STREAMTOHEXTEXT function. Input Formats The following table shows an example of input in its character text representation as viewed through the character editor, and in its ASCII code representation (binary text stream) as viewed through the hex editor.
Chapter 3 - Function Reference HEXTEXTTOSTREAM character editor. (The hex view of the input is 0x3431203432203433203434. The hex view of the output is 0x41424344.) HEXTEXTTOSTREAM ("0D 0A 00") Returns the evaluated value of the input (ASCII) character text string “0D 0A 00” as the output (ASCII) character text string “” as viewed in the character editor. (The hex view of the input is 0x3044203041203030. The hex view of the output is 0x0D0A00.
Chapter 3 - Function Reference IF IF The IF function evaluates a conditional expression, returning one value if true, another if false. Syntax IF (single-condition-expression , single-general-expression [ , single-general-expression ]) Meaning IF (test_this , result_if_true [ , result_if_false ]) Returns This function returns a single-item or single-group. If test_this evaluates to TRUE, the result is result_if_true. Otherwise, if test_this evaluates to FALSE, the result is result_if_false.
Chapter 3 - Function Reference INDEX INDEX The INDEX function returns an integer that represents the index of an object relative to its nearest contained object, counting only valid objects. Syntax INDEX (single-object-name) Meaning INDEX (object_for_which_to_get_index) Returns This function returns a single-integer. The result is the index of object_for_which_to_get_index. ! If object_for_which_to_get_index is an input, this will be the index within all valid objects.
Chapter 3 - Function Reference INDEX Uses Use INDEX when you need to select or test particular objects based on their occurrence, as in the example above. Or, use INDEX to add a sequence number to output objects. Note INDEX cannot be used in a component rule.
Chapter 3 - Function Reference INDEXABS INDEXABS The INDEXABS function returns an integer that represents the index of an object relative to its nearest contained object, counting both valid and invalid instances of the object. Syntax INDEXABS (single-object-name) Meaning INDEXABS (object_for_which_to_get_index) Returns This function returns a single-integer. INDEXABS returns an integer that represents the absolute index of object_for_which_to_get_index.
Chapter 3 - Function Reference INDEXABS In a map rule, INDEXABS (MessageRecord[3]:Order:PO_File) would evaluate to 3. Note If the INDEX function was used, INDEX ( Message Record[3]:Order:PO ) would evaluate to 2. Uses Use INDEXABS when you need the absolute occurrence of a particular object across all occurrences, rather than across only valid occurrences.
Chapter 3 - Function Reference INT INT The INT function returns the integer portion of a number. Syntax INT (single-number-expression) Meaning INT (number_to_convert) Returns This function returns a single-integer. The result is the integer part of number_to_convert. Any fractional part after the decimal point is dropped. Examples INT (1.45) returns 1 INT (3.6) returns 3 INT (Purchase:Amt – Discount:Amt) subtracts Discount:Amt from Purchase:Amt and returns the result as a whole number.
Chapter 3 - Function Reference ISALPHA ISALPHA The ISALPHA function tests a text object to see if it is contains all alphabetic characters. Syntax ISALPHA (single-text-expression) Meaning ISALPHA (text_to_test) Returns This function returns TRUE or FALSE If text_to_test contains only alphabetic characters (for example, A-Z and a-z), ISALPHA evaluates to TRUE. If text_to_test contains other than just alphabetic characters, ISALPHA returns FALSE. Examples ISALPHA ("AnywhereUSA") Returns TRUE.
Chapter 3 - Function Reference ISERROR ISERROR The ISERROR function tests an object to see if it is in error. Syntax ISERROR (single-object-name) Meaning ISERROR (object_to_test) Returns This function returns TRUE or FALSE Returns TRUE if object_to_test is in error. Returns FALSE if object_to_test is completely valid.
Chapter 3 - Function Reference ISLOWER ISLOWER The ISLOWER function tests a text object to see if it contains all lowercase alphabetic characters. Syntax ISLOWER (series-text-expression) Meaning ISLOWER (text_to_test) Returns This function returns TRUE or FALSE If text_to_test contains only lowercase alphabetic characters (for example, a-z), ISLOWER evaluates to TRUE. If text_to_test contains other than lowercase alphabetic characters, ISLOWER returns FALSE. Examples ISLOWER ("mercator") returns TRUE.
Chapter 3 - Function Reference ISNUMBER ISNUMBER The ISNUMBER function tests a text object to determine whether it contains all numeric characters. Syntax ISNUMBER (single-text-expression) Meaning ISNUMBER (text_to_test) Returns This function returns TRUE or FALSE If text_to_test contains only digits (for example, 0-9), ISNUMBER evaluates to TRUE. If text_to_test contains other than digits, ISNUMBER evaluates to FALSE.
Chapter 3 - Function Reference ISUPPER ISUPPER The ISUPPER function tests a text object to determine whether it contains all uppercase alphabetic characters. Syntax ISUPPER (series-text-expression) Meaning ISUPPER (text_to_test) Returns This function returns TRUE or FALSE If text_to_test contains only uppercase alphabetic characters (for example, A-Z), ISUPPER evaluates to TRUE. If text_to_test contains other than uppercase alphabetic characters, ISUPPER returns FALSE.
Chapter 3 - Function Reference LASTERRORCODE LASTERRORCODE The LASTERRORCODE function returns a text item whose value is the last error code returned by one of a specified set of functions during map execution. Syntax LASTERRORCODE ( ) Meaning LASTERRORCODE ( ) Note Although LASTERRORCODE has no arguments, it does require parentheses. Returns This function returns a single-text-item.
Chapter 3 - Function Reference LASTERRORCODE Uses Use LASTERRORCODE to interrogate or report the error code returned by one of the external interface functions.
Chapter 3 - Function Reference LASTERRORMSG LASTERRORMSG The LASTERRORMSG function returns a text item whose value is the message corresponding to the last error code returned by one of a specified set of functions during map execution. Syntax LASTERRORMSG ( ) Meaning LASTERRORMSG ( ) Note Although LASTERRORMSG has no arguments, it does require parentheses. Returns This function returns a single-text-item.
Chapter 3 - Function Reference LASTERRORMSG (8): One or more inputs was invalid is reported in the execution audit log. Uses Use LASTERRORMSG to interrogate or report the message corresponding to the error code returned by one of the external interface functions.
Chapter 3 - Function Reference LEAVEALPHA LEAVEALPHA The LEAVEALPHA function removes all non-alphabetic characters from a specified text item. Syntax LEAVEALPHA (single-text-expression) Meaning LEAVEALPHA (text_to_change) Returns This function returns a single-text-item. Returns a string containing only the alphabetic characters (for example, A-Z and a-z) in text_to_change.
Chapter 3 - Function Reference LEAVEALPHANUM LEAVEALPHANUM The LEAVEALPHANUM function removes all non-alphanumeric characters from a specified text item. Syntax LEAVEALPHANUM (single-text-expression) Meaning LEAVEALPHANUM (text_to_change) Returns This function returns a single-text-item. Returns a string containing only the alphanumeric characters (e.g., A-Z, a-z and 0-9) in text_to_change.
Chapter 3 - Function Reference LEAVENUM LEAVENUM The LEAVENUM function removes all non-numeric characters from a text item. Syntax LEAVENUM (single-text-expression) Meaning LEAVENUM (text_to_change) Returns This function returns a single-text-item. Returns a string containing only the numeric characters (for example, 0-9) in text_to_change.
Chapter 3 - Function Reference LEAVEPRINT LEAVEPRINT The LEAVEPRINT function removes all non-printable characters from a text item. Syntax LEAVEPRINT (single-text-expression) Meaning LEAVEPRINT (text_to_change) Returns This function returns a single-text-item. Returns a string containing only printable characters in text_to_change. Example LEAVEPRINT ("A-bC-1$3") returns A-b C-1$3 Uses Use LEAVEPRINT to remove all non-printable characters from a text string.
Chapter 3 - Function Reference LEFT LEFT The LEFT function returns a specified number of characters from a text expression beginning with the leftmost byte of a text item. Syntax LEFT (single-text-expression , single-integer-expression) Meaning LEFT (text_to_extract_from , number_of_characters_to_extract) Returns This function returns a single-text-item.
Chapter 3 - Function Reference LOOKUP characters are needed. LEFT can be used to return only the leftmost 10 characters. Related Functions RIGHT, MID, FIND LOOKUP The LOOKUP function sequentially searches a series, returning the first member of the series that meets a specified condition. Syntax LOOKUP (series-object-expression , single-conditionexpression) Meaning LOOKUP (series_to_search , condition_to_evaluate) Returns This function returns a single-object.
Chapter 3 - Function Reference LOOKUP LOOKUP performs a sequential search over series_to_search. Use LOOKUP if series_to_search is not ordered. Using SEARCHUP when that series is in ASCII ascending order or SEARCHDOWN when that series is in ASCII descending order can save time if the series being searched is large.
Chapter 3 - Function Reference LOWERCASE LOWERCASE The LOWERCASE function converts a text item to all lowercase characters. Syntax LOWERCASE (single-text-expression) Meaning LOWERCASE (text_to_convert) Returns This function returns a single-text-item. Produces a text item in which each byte from the input has been converted to lowercase. Any numeric or symbol characters in the text item remain unchanged.
Chapter 3 - Function Reference MAX MAX The MAX function returns the maximum value from a series. Syntax MAX (series-item-expression) Meaning MAX (series_of_which_to_find_max) Returns This function returns a single-number. The result is the maximum value in the input argument series: number, text, or date/time. Example MAX ( UnitPrice:Input ) If the values for UnitPrice are {20, 10, 100}, MAX returns 100.
Chapter 3 - Function Reference MEMBER MEMBER The MEMBER function searches a series, looking for a single specified object in the series. If any object in the series matches the specified object, MEMBER returns TRUE. If there is no match, MEMBER returns FALSE.
Chapter 3 - Function Reference MID Uses Use MEMBER when you need to know whether an object occurs within a series. Related Functions EXTRACT, LOOKUP MID The MID function returns one or more characters from a text item. Syntax MID (single-text-expression, single-number-expression, single-number-expression) Meaning MID (source_text, position_to_start_the_search, number_of_characters) Returns This function returns a single-text-item.
Chapter 3 - Function Reference MIN MID ("abc123", -1 , 3) returns NONE because argument2 is a negative number. MID ("abc123", 2 , -2) returns NONE because argument3 is a negative number. Uses Use MID when you need specific characters from a text item. Related Functions FIND, LEFT, RIGHT MIN The MIN function returns the minimum value from a series. Syntax MIN (series-item-expression) Meaning MIN (series_of_which_to_find_min) Returns This function returns a single-number.
Chapter 3 - Function Reference MOD Uses Use MIN when you need the minimum value from a series of number, date, time, or text values. Related Function MAX MOD The MOD function returns the modulus that remains after a number is divided by an integer. Syntax MOD (single-number-expression , single-integer-expression) Meaning MOD (dividend , divisor) Returns This function returns a single-integer. The result is the remainder (modulus) after dividend is divided by divisor.
Chapter 3 - Function Reference NOT NOT The NOT function tests a condition and returns TRUE if the condition is not true and FALSE if the condition is true. Syntax NOT (single-condition-expression) Meaning NOT (condition_to_evaluate) Returns This function returns TRUE or FALSE Returns TRUE if the condition evaluates to FALSE. Returns FALSE if the condition evaluates to TRUE. Examples NOT (Qty:.
Chapter 3 - Function Reference NUMBERTODATE NUMBERTODATE The NUMBERTODATE function converts an integer to a date, where the integer is the number of days from December 31, 1864, to the specified date. Syntax NUMBERTODATE (single-integer-expression) Meaning NUMBERTODATE (integer_to_convert) Returns This function returns a single-date. This function converts an integer to a date. If the result is being assigned to a date object, the resulting date is in the presentation for that output.
Chapter 3 - Function Reference NUMBERTOTEXT NUMBERTOTEXT The NUMBERTOTEXT function converts a number to text that looks like the original object. Syntax NUMBERTOTEXT (single-number-expression) Meaning NUMBERTOTEXT (number_to_convert) Returns This function returns a single-text-item. Converts a character number to a text item. The resulting text looks like the input argument. The result is truncated, if necessary. Example NUMBERTOTEXT (ROUND (1000 - 24.
Chapter 3 - Function Reference OFFSET OFFSET The OFFSET function returns an integer representing the offset of the specified object within the data. Syntax OFFSET (single-object-expression) Meaning OFFSET (object_whose_offset_is_needed) Returns This function returns a single-integer. Returns the offset, in bytes, of the specified object within its card object, beginning at offset 0. For an object that has an initiator, the offset will apply to the first byte of the data.
Chapter 3 - Function Reference ONERROR ONERROR The ONERROR function adds a user-defined error message to the data section of the audit log. This function is relevant only in Type Designer Component rules. Syntax ONERROR (single-condition-expression , single-textexpression) Meaning ONERROR (condition_to_evaluate , message_to_display) Returns This function returns TRUE or FALSE. If condition_to_evaluate evaluates to TRUE, the function returns TRUE.
Chapter 3 - Function Reference ONERROR If the component rule fails, the data section of the audit log shows the following information:
Chapter 3 - Function Reference OR Uses Use ONERROR in component rules to add user-defined error messages to the data section of the audit log. OR The OR function evaluates a series of conditions and returns TRUE if at least one evaluates to TRUE; otherwise returns FALSE. Syntax OR (series-condition-expression) Meaning OR (conditions_to_evaluate) Returns This function returns TRUE or FALSE Evaluates to TRUE if any member of the argument evaluates to TRUE.
Chapter 3 - Function Reference PACK PACK The PACK function converts an integer to a text item that can be interpreted as a packed decimal number. The sign values for packed data are as follows: ♦ C for positive (+) ♦ D for negative (-) ♦ F for unsigned, which is read as positive Syntax PACK (single-integer-expression) Meaning PACK (integer_to_conver) Returns This function returns a single-text-item.
Chapter 3 - Function Reference PACKAGE Note Define items as having a packed decimal number presentation. Then, when mapping to or from these items, the conversion to and from packed decimal is automatically performed as needed. Related Function UNPACK PACKAGE The PACKAGE function converts a group or item object to a text item, including its initiator and terminator. Syntax PACKAGE (single-object-expression) Meaning PACKAGE (object_to_convert) Returns This function returns a single-text-item.
Chapter 3 - Function Reference PARTITION PACKAGE differs from TEXT because it includes the initiator and terminator of the input object. Related Functions DATETOTEXT, NUMBERTOTEXT, SERIESTOTEXT, TIMETOTEXT, TEXT PARTITION The PARTITION function checks to see if an occurrence of an object belongs to a certain partition. If the object is that partition, TRUE is returned. Otherwise, FALSE is returned.
Chapter 3 - Function Reference PRESENT Related Function GETPARTITIONNAME PRESENT The PRESENT function tests for the presence of an object. Syntax PRESENT (single-object-expression) Meaning PRESENT (object_to_look_for) Returns This function returns TRUE or FALSE Returns TRUE if the input argument does not evaluate to NONE; the object is present. Returns FALSE if the input argument evaluates to NONE; the object is not present.
Chapter 3 - Function Reference PROPERCASE Uses PRESENT is commonly used with the IF function in a map rule to provide conditional logic. For example, if the object is present, do this; otherwise, do something else. Similarly, PRESENT is commonly used with the WHEN function in component rules to provide conditional validation logic.
Chapter 3 - Function Reference PUT Uses Use PROPERCASE to convert the first alphabetic character in each word to uppercase and all remaining characters to lowercase. Related Functions ISALPHA, ISLOWER, ISUPPER, LOWERCASE, UPPERCASE PUT The PUT function passes data to a target adapter. Note The PUT function has a scope of Burst.
Chapter 3 - Function Reference PUT Examples The example that follows illustrates a mapping situation in which a set of messages is being produced in the output (output card #1). However, the ultimate target for these messages is a message queue and the messages need to be placed on the queue one at a time. To accomplish this, the first output card builds the set of messages and its Target is defined as Sink. So, this set of messages is constructed in memory, but is discarded after the map completes.
Chapter 3 - Function Reference REFORMAT Uses Use PUT to route data within your map using one of the target adapters such as to a messaging system, a database, a file, and so on. Related Functions FAIL, GET, LASTERRORCODE, LASTERRORMSG, VALID REFORMAT The REFORMAT function returns a type object that results from replacing the syntax of the input type with the syntax of the output type. The initiator and terminator of the output type are built.
Chapter 3 - Function Reference REFORMAT Returns This function returns a type object. Input Type Output Type Result Group Group The content for each output component results from the content of the corresponding input group component content. For example, output component 1 is matched with input component 1, output component 2 is matched with input component 2, and so on. The components of the groups must be items, not another group.
Chapter 3 - Function Reference REJECT REJECT The REJECT function returns the content of an object in error as a text item. Syntax REJECT (series-object-expression) Meaning REJECT (series_to_look_for_bad_objects) Returns This function returns a series-text-item. Evaluates to a series of text items consisting of all the input series members in error. Examples REJECT (Record:File) This example extracts all Records that are in error.
Chapter 3 - Function Reference REVERSEBYTE REVERSEBYTE The REVERSEBYTE function reverses the byte order of an item. Syntax REVERSEBYTE (single-item-expression) Meaning REVERSEBYTE (item_to_reverse) Returns This function returns a single-item. This function reverses the byte order of item_to_reverse. Example REVERSEBYTE ("HI MOM!") returns "!MOM IH" RIGHT (FullName, FIND (" " , REVERSEBYTE (FullName)) - 1) If FullName is "Alyce N.
Chapter 3 - Function Reference RIGHT RIGHT The RIGHT function returns a specified number of characters from a text expression beginning with the rightmost byte of a text item. Syntax RIGHT (single-text-expression , single-integer-expression) Meaning RIGHT (text_to_extract_from , number_of_characters_to_extract) Returns This function returns a single-text-item.
Chapter 3 - Function Reference ROUND Uses Use RIGHT when you need a specific part of a text item. For example, a customer number may have several uses and sometimes only the last three characters are needed. RIGHT can be used to return only the rightmost three characters. Related Functions FIND, LEFT, MID ROUND The ROUND function rounds a number to a specified number of decimal places. If the number of decimal places is not specified, the number is rounded to a whole number.
Chapter 3 - Function Reference RUN Uses Use ROUND to round a number to a specified number of decimal places. Also, you can use ROUND to round to a whole number by not specifying a second argument. Related Function TRUNCATE RUN The RUN function allows you to execute another compiled map from a component or map rule.
Chapter 3 - Function Reference RUN ! If you use the echo command option for more than one output card, the data from all echoed cards will be concatenated together and passed back as a text-item to the object in the map from which it was run. ! If you do not use the echo option, the return code indicating the status of the map that was run will be passed back to the object in the map from which it was run.
Chapter 3 - Function Reference " -OE2" SEARCHDOWN Using the output echo command option, the data built for output card 2 of the DoOneSet map will be returned as the result of the RUN function. Uses Use RUN to dynamically name source and/or destination files or to dynamically pass data to a map. Another possible use of RUN is to split the output data into separate files based on some value in the input.
Chapter 3 - Function Reference SEARCHDOWN Examples SEARCHDOWN ( Age Column:Row:DBSelect , SSN Column:Row:DBSelect , SSN_Value:Message ) If there are ten Rows in DBSelect, the search starts by comparing the first SSN Column of the fifth Row with the SSN_Value in Message. If the result matches, SEARCHDOWN returns the first Age Column of that Row. If the value of SSN Column is less than the SSN_Value in Message, the search continues with the third Row.
Chapter 3 - Function Reference SEARCHUP SEARCHUP The SEARCHUP function performs a binary search on a series sorted in ASCII ascending order, returning a related object that corresponds to the item found. Syntax SEARCHUP (series-object-expression, series-item-objectexpression, single-item-expression) Meaning SEARCHUP (corresponding_object_to_return, ascending_items_to_search, item_to_match) Returns This function returns a single-object.
Chapter 3 - Function Reference SEARCHUP Uses Use SEARCHUP when data is sorted in ASCII ascending order and you need to look up data within the sorted data using the value on which the data is sorted.
Chapter 3 - Function Reference SERIESTOTEXT SERIESTOTEXT The SERIESTOTEXT function converts a contiguous or non-contiguous series to a text item. Syntax SERIESTOTEXT (series-object-expression) Meaning SERIESTOTEXT (series_to_convert) Returns This function returns a single-text-item. Returns a text item containing the concatenation of the series of the input argument, including nested delimiters but excluding initiators and terminators.
Chapter 3 - Function Reference SERIESTOTEXT = BowlerName Field:Bowler:Input + " ->" + SERIESTOTEXT (Score Field:Bowler:Input) the following output would be produced: Andrews, Jessica -> 145138177159 Little, Randy -> 175168 Wayne, Richard -> 185204179164212 In this example, you have an input number that is of variable size, followed by a name. There is no syntax that separates the number from the name.
Chapter 3 - Function Reference SETOFF SETOFF The SETOFF function sets to zero a specified bit in a binary number. Syntax SETOFF ( single-binary-number-expression, single-integer-expression ) Meaning SETOFF ( binary_number_to_change, bit_to_turn_off ) Returns This function returns a single-binary-number. SETOFF uses bit_to_turn_off to specify the bit of binary_number_to_change to be set to the value 0. The result is a binary number item of the same size as binary_number_to_change.
Chapter 3 - Function Reference SETOFF Uses Use SETOFF to manipulate (turn off) a bit in a binary number.
Chapter 3 - Function Reference SETON SETON The SETON function sets to one a specified bit in a binary number. Syntax SETON ( single-binary-number-expression, single-integerexpression ) Meaning SETON ( binary_number_to_change, bit_to_turn_on ) Returns This function returns a single-binary-number. SETON uses the value of bit_to_turn_on to specify the bit of binary_number_to_change that should be set to the value 1. The result is a binary number item of the same size as binary_number_to_change.
Chapter 3 - Function Reference SETON Uses Use SETON to turn on a specific bit in a binary number.
Chapter 3 - Function Reference SIZE SIZE The SIZE function returns an integer representing the size of a specified object, exclusive of any pad characters. Syntax SIZE (single-object-expression ) Meaning SIZE (object_whose_size_is_needed ) Returns This function returns a single-integer. Return the size, in bytes, of object_whose_size_is_needed. The size returned does not include any pad characters that may be in the object.
Chapter 3 - Function Reference SORTDOWN SORTDOWN The SORTDOWN function returns a series containing the values from an input series in ASCII descending order. Syntax SORTDOWN (series-item-expression) Meaning SORTDOWN (item_series_to_sort) Returns This function returns a series-object. Returns the values in item_series_to_sort in ASCII descending order.
Chapter 3 - Function Reference SORTDOWN Original Series SORTDOWN SORTUP oysters aaaaa Clams Casino raw oysters aa Grouper clams a Rock Lobster SHRIMP Snapper SHRIMP 1 Shrimp Snapper Shark Fin Soup 22 Shrimp Shrimp Shrimp A 1 A shrimp Shark Fin Soup Snapper A1A Shrimp SHRIMP Snapper AAAAA Rock Lobster a aaaaa Grouper aa aa Clams Casino aaaaa AA AAAAA clams A AA groupers a A1A Shrimp lobster Rock Lobster A 1 A shrimp lobster tails Snapper A oysters Snapper
Chapter 3 - Function Reference SORTUP SORTUP The SORTUP function returns a series containing the values from an input series in ASCII ascending order. Syntax SORTUP ( series-item-expression ) Meaning SORTUP ( item_series_to_sort ) Returns This function returns a series-object. Returns the values in item_series_to_sort in ASCII ascending order. Example SORTUP ( Abbr:File ) In this example, if Abbr had the values ABC, GHI, DEF, the SORTUP function would return these values as a series as ABC, DEF, GHI.
Chapter 3 - Function Reference SQRT SQRT The SQRT function returns the square root of a number. Syntax SQRT ( single-number-expression ) Meaning SQRT ( number ) Returns This function returns a single-number. Returns the square root of number. Example SQRT ( 4 ) returns 2 Uses Use SQRT to return the square root of a number.
Chapter 3 - Function Reference SQUEEZE SQUEEZE The SQUEEZE function removes consecutive duplicate occurrences of a specified character or characters from a text item. Syntax SQUEEZE ( single-text-item , single-text-item ) Meaning SQUEEZE ( text_to_squeeze , duplicate_characters_to_remove ) Returns This function returns a single-text-item. Returns a text item with all the consecutive duplicates of duplicate_characters_to_remove removed from text_to_squeeze.
Chapter 3 - Function Reference STREAMTOHEXTEXT STREAMTOHEXTEXT The STREAMTOHEXTEXT function returns a character text string represented by hex pairs whose value is the evaluation of an input binary text stream. The STREAMTOHEXTEXT function is the reverse of the HEXTEXTTOSTREAM function.
Chapter 3 - Function Reference STREAMTOHEXTEXT as viewed in the hex editor. (The character view of the input is “ABCD”. The character view of the output is “41424344”.) STREAMTOHEXTEXT (0x0D0A00) Returns the evaluated value of the input binary text stream 0x0D0A00 as the output 0x304430413030 as viewed in the hex editor. (The character view of the input is “”. The character view of the output “0D0A00”.) Uses Use STREAMTOHEXTEXT to assign a character text value to a binary text stream item.
Chapter 3 - Function Reference SUBSTITUTE SUBSTITUTE The SUBSTITUTE function replaces specified text with other specified text. Note The SUBSTITUTE function has been extended to include multiple pairs. Syntax SUBSTITUTE ( single-text-expression { , single-textexpression , single-text-expression } ) Meaning SUBSTITUTE (item_to_convert, one-or-more-text-substitutionpairs) Where each one-or-more-text substitution-pair is text_to_change , substitute_text Returns This function returns a single-text-item.
Chapter 3 - Function Reference SUM Uses Use SUBSTITUTE to replace a character with another character or to remove a character. Related Functions LEAVEALPHA, LEAVEALPHANUM, LEAVENUM, LEAVEPRINT, SQUEEZE SUM The SUM function calculates the sum of a series of numbers. Syntax SUM ( series-number-expression ) Meaning SUM ( series_to_sum ) Returns This function returns a single-number. The result is the sum of all the members in series_to_sum.
Chapter 3 - Function Reference SYMBOL SYMBOL The SYMBOL function returns a one byte character that is the ASCII character equivalent of a specified decimal value. Syntax SYMBOL ( single-integer-expression ) Meaning SYMBOL ( decimal_value ) Returns This function returns a single-one-byte-text-item. The value of the resulting item is the ASCII character equivalent of decimal_value. Valid input values are 0 to 255. Values outside of this range return NONE.
Chapter 3 - Function Reference TESTOFF TESTOFF The TESTOFF function tests a specified bit in a binary number to see whether it is off. Syntax TESTOFF ( single-binary-number-expression , single-integer-expression ) Meaning TESTOFF ( binary_number_to_test , bit_to_test ) The value of bit_to_test specifies which bit of binary_number_to_test should be tested for the value 0. If bit_to_test has the value 1, it refers to the leftmost bit of binary_number_to_test.
Chapter 3 - Function Reference TESTON TESTON The TESTON function tests a specified bit in a binary number to see whether it is on. Syntax TESTON ( single-binary-number-expression, single-integer-expression ) Meaning TESTON ( binary_number_to_test ,bit_to_test ) The value of bit_to_test specifies the bit of binary_number_to_test to test for the value 1. If bit_to_test has the value 1, it refers to the leftmost bit of binary_number_to_test.
Chapter 3 - Function Reference TESTON Uses Use TESTON to test whether a specified bit is on in a binary number item.
Chapter 3 - Function Reference TEXT TEXT The TEXT function converts the content of a group or item object to a text item. Syntax TEXT ( single-object-expression ) Meaning TEXT ( object_to_convert ) Returns This function returns a single-text-item. Converts object_to_convert to a text-item, excluding the initiator and terminator of the input object. Examples In this example, the group Record has an initiator of the pound sign #, a terminator of the at-sign @, and a delimiter of a comma ,.
Chapter 3 - Function Reference TEXTTOBCD TEXTTOBCD The TEXTTOBCD function converts a text item from decimal digits to BCD (Binary Coded Decimal) format. Syntax TEXTTOBCD ( single-integer-text-expression ) Meaning TEXTTOBCD ( text_to_be_converted ) Returns This function returns a single-BCD-formatted-text-item. TEXTTOBCD converts text_to_be_converted (which consists of decimal digits) to BCD format. In this format, each byte contains two decimal digits represented as binary numbers.
Chapter 3 - Function Reference TEXTTOBCD Uses Use TEXTTOBCD to convert a character number into BCD format.
Chapter 3 - Function Reference TEXTTODATE TEXTTODATE The TEXTTODATE function converts text that is in CCYYMMDD or YYMMDD format to a date. Syntax TEXTTODATE ( single-text-expression ) Meaning TEXTTODATE ( text_to_convert_to_date ) Returns This function returns a single-date. The text_to_convert_to_date must be in either CCYYMMDD or YYMMDD format. If the text_to_convert_to_date is in error (for example, it is not a valid date), the result is NONE.
Chapter 3 - Function Reference TEXTTONUMBER TEXTTONUMBER The TEXTTONUMBER function converts an object defined as text to an object defined as a character number item. Syntax TEXTTONUMBER ( single-text-expression ) Meaning TEXTTONUMBER ( text_to_convert_to_number ) Returns This function returns a single-character-number. The text_to_convert_to_number must be in integer or ANSI-formatted (floating point) presentation.
Chapter 3 - Function Reference TEXTTOTIME TEXTTOTIME The TEXTTOTIME function converts an object defined as text to an object defined as time. The text object to be converted must be in HHMM or HHMMSS presentation. Syntax TEXTTOTIME ( single-text-expression ) Meaning TEXTTOTIME ( text_to_convert_to_time ) Returns This function returns a single-time. The text_to_convert_to_time must be in HHMM or HHMMSS presentation. HH is a two-digit hour in a 24-hour format.
Chapter 3 - Function Reference TIMETOTEXT TIMETOTEXT The TIMETOTEXT function converts a time object or expression to a text item. Syntax TIMETOTEXT ( single-time-expression ) Meaning TIMETOTEXT ( time_to_convert_to_text ) Returns This function returns a single-text-item. If time_to_convert_to_text is a time object name, this returns the time as a text item formatted according to the presentation of the input date object.
Chapter 3 - Function Reference TOBASETEN TOBASETEN The TOBASETEN function converts a text item that can be interpreted as a number, using positional notation of the base specified, to a base 10 number. Syntax TOBASETEN ( single-text-expression , single-integerexpression ) Meaning TOBASETEN ( text_to_convert , base_to_convert_from ) Returns This function returns a single-integer.
Chapter 3 - Function Reference TODATETIME TODATETIME The TODATETIME function converts a text string of a specified format to a date/time. Syntax TODATETIME ( single-character-text-expression [ , single-text-expression ] ) Meaning TODATETIME ( text_to_convert [ , date_time_format_string ] ) Returns This function returns a single-character_date_item. Returns the date/time that corresponds to the value specified by text_to_convert, which is in the format specified by date_time_format_string.
Chapter 3 - Function Reference TONUMBER Uses Use TODATETIME to convert a text string to a date/time item. Related Functions CURRENTDATE, CURRENTDATETIME, CURRENTTIME, TEXTTODATE, TEXTTOTIME TONUMBER The TONUMBER function converts a text string of a specified format to a number. Syntax TONUMBER ( single-character-text-expression [ , single-text-expression ] ) Meaning TONUMBER ( text_to_convert [ , number_format_string ] ) Returns This function returns a single-character-number-item.
Chapter 3 - Function Reference TONUMBER TONUMBER(text_to_convert, "{L+'$'#','###}") The L+'$' indicates the leading dollar sign is positive. That leading sign and the comma separators are removed when the text is converted to a number. Input String $123,000,000 Output Number Explanation 123000000 The $ and commas are removed. TONUMBER(text_to_convert, "{####T-}" Four number signs (#) are required for each whole number, regardless of the actual number of digits in the number.
Chapter 3 - Function Reference TONUMBER TONUMBER(text_to_convert, "{L-'('#','###T-')'}" The parentheses indicating a negative number are removed and replaced with a negative sign. Comma separators are removed when the text is converted to a number. Input String (12,345) Output Number Explanation -12345 The output becomes a negative number. The comma separator is removed. 67890 The comma separator is removed. 67,890 -345 The output becomes a negative number.
Chapter 3 - Function Reference TRIMLEFT TRIMLEFT The TRIMLEFT function removes leading characters from a text item. Syntax TRIMLEFT ( single-text-expression [ , single-text-expression ]) Meaning TRIMLEFT ( item_to_trim [ , text_to_trim ] ) Returns This function returns a single-text-item. Removes leading characters that match text_to_trim from item_to_trim and returns the result as a single-text-item. If text_to_trim is not specified, leading spaces are removed from item_to_trim.
Chapter 3 - Function Reference TRIMRIGHT TRIMRIGHT The TRIMRIGHT function removes trailing characters from a text item. Syntax TRIMRIGHT ( single-text-expression [ , single-text-expression ]) Meaning TRIMRIGHT ( item_to_trim [ , text_to_trim ] ) Returns This function returns a single-text-item. Removes trailing characters that match text_to_trim from item_to_trim and returns the result as a single-text-item. If text_to_trim is not specified, trailing spaces are removed from item_to_trim.
Chapter 3 - Function Reference TRUNCATE TRUNCATE The TRUNCATE function removes decimal places from a number, leaving a specified number of decimal places. Syntax TRUNCATE (single-number-expression[ , single-integerexpression ] ) Meaning TRUNCATE (number_to_truncate[ , number_of_decimal_places ] ) Returns This function returns a single-number. TRUNCATE first converts number_to_truncate to character format, if necessary.
Chapter 3 - Function Reference UNIQUE UNIQUE The UNIQUE function returns a series containing all the unique members of a series. Syntax UNIQUE ( series-object-expression ) Meaning UNIQUE ( series_to_evaluate ) Returns This function returns a series-object. Evaluates to a series containing all the unique members of series_to_evaluate. Evaluates to NONE if series_to_evaluate evaluates to NONE. Examples UNIQUE ( PartNumber:Inventory:File ) returns the unique PartNumbers in Inventory:File.
Chapter 3 - Function Reference UNPACK UNPACK The UNPACK function converts text that can be interpreted as a packed decimal number to a signed integer item. The sign values for packed data are as follows: ♦ C for positive (+) ♦ D for negative (-) ♦ F for unsigned, which is read as positive Syntax UNPACK ( single-fixed-size-text-expression ) Meaning UNPACK ( text_to_unpack ) Returns This function returns a single-signed-integer.
Chapter 3 - Function Reference UNZONE Uses Use UNPACK to do arithmetic with a packed decimal number or to move a packed decimal value into a numeric item. Note You can define items as having a packed decimal number presentation. Then, when mapping to or from these items, the conversion to and from packed decimal is automatically performed as needed.
Chapter 3 - Function Reference UNZONE Zoned integers have a series of digits except for the rightmost byte. The rightmost byte is a digit with a super-imposed sign. See ZONE for a list of rightmost byte values. Examples UNZONE ( "123D" ) returns 1234 UNZONE ( "1234" ) returns 1234 UNZONE ( TaxRate ) / 1000 * Income TaxRate is converted from zoned format to a signed decimal and divided by 1000 (to convert it to a number with three decimal places), and then multiplied by Income.
Chapter 3 - Function Reference UPPERCASE UPPERCASE The UPPERCASE function converts text to all uppercase characters. Syntax UPPERCASE ( single-text-expression ) Meaning UPPERCASE ( text_to_convert ) Returns This function returns a single-text-item. Produces a text item in which each byte in text_to_convert has been converted to uppercase. Any numeric or symbolic characters in text_to_convert remain unchanged.
Chapter 3 - Function Reference VALID VALID The VALID function returns the result of the first argument if it is valid; otherwise, returns the second argument. Syntax VALID ( single-text-expressions , single-general-expression ) Meaning VALID ( function_that_can_fail , return_value_if_function_fails ) Returns This function returns a single-text-expression. VALID returns the result of the evaluation of function_that_can_fail if it is valid.
Chapter 3 - Function Reference WHEN WHEN The WHEN function evaluates a condition. Then, based on that evaluation, evaluates another condition and returns TRUE or FALSE. Syntax WHEN ( single-condition-expression , single-conditionexpression [ , single-condition-expression ] ) Meaning WHEN ( condition1 , condition2 [ , condition3 ] ) Returns This function returns TRUE or FALSE Returns TRUE if condition1 and condition2 both evaluate to TRUE.
Chapter 3 - Function Reference WHEN Examples WHEN (ABSENT(CatalogueField), ABSENT (QuantityField)) Returns TRUE when CatalogueField and QuantityField are both absent. WHEN (PRESENT(ShipDate), PRESENT(InStock), PRESENT(BackOrderDate )) Returns FALSE when ShipDate and BackOrderDate are both absent. Uses Use WHEN in a component rule to test for the validity of one object based on another object.
Chapter 3 - Function Reference WORD WORD The WORD function returns the characters between two user-defined separators within a text item. The separators are counted from left to right when the third argument is positive, and from right to left when the third argument is negative, enabling the function to search from either end of the item.
Chapter 3 - Function Reference WORD WORD ( Line1:Letter , " " , 6 ) If the nth separator is missing and the nth-1 separator exists, the function returns the characters between the last separator and the end of the delimited text item. In this example, WORD returns "winner!" The separator is a space and "winner!, including the exclamation point, is the sixth word. WORD ( Line2:Letter , "!" , 3 ) If both nth and nth-1 separators are missing, as in this example, WORD returns NONE.
Chapter 3 - Function Reference ZONE ZONE The ZONE function converts a signed integer item to a text item that can be interpreted as a number with a superimposed sign in the rightmost byte (called zoned or signed). Syntax ZONE ( single-integer-expression , single-integer-expression ) Meaning ZONE ( integer_to_convert , sign_indicator ) Returns This function returns a single-text-item. Returns a text string that represents a zoned (signed) number representing integer_to_convert.
Chapter 3 - Function Reference ZONE Positive Zoned Value Integer Value sign_indicator = 0 sign_indicator = 1 1236 1236 123F 1237 1237 123G 1238 1238 123H 1239 1239 123I Negative Zoned Value Integer Value sign_indicator = 0 sign_indicator = 1 -1230 123} 123} -1231 123J 123J -1232 123K 123K -1233 123L 123L -1234 123M 123M -1235 123N 123N -1236 123O 123O -1237 123P 123P -1238 123Q 123Q -1239 123R 123R Examples ZONE ( 1234 , 0 ) returns 1234.
Chapter 3 - Function Reference ZONE UnitPrice is multiplied by 100 to move the first two decimal places to the left of the decimal sign. The result of this calculation is converted to an integer using the INT function. Finally, the result of the INT is converted to zoned format, using a sign for positive values. Uses Use ZONE to convert a number to a zoned (signed) number. Note You can define items as having a zoned character number presentation.
Chapter 4 - Implementing a Library EXIT Function This chapter provides information on how to develop a function in a library that will be executed from an EXIT function. EXIT Function’s Library Interface Library functions are used within an EXIT function using information contained in the EXITPARAM structure. This method provides great flexibility for data passed to and from a map. For example, the map can pass binary data containing nulls, and there is no limitation on the length of the returned data.
Chapter 4 - Implementing a Library EXIT Function BOOL int char char void FAR void FAR void FAR void FAR void FAR void FAR void FAR void FAR DWORD LPSTR WORD WORD WORD UINT BOOL BOOL BOOL DWORD * * * * * * * * Using the EXITPARAM Structure bCleanup; nReturn; szErrMsg[100]; szFile[260]; lpMapHandle; lpInternal; lpCmdStruct; lpAdaptParms; lpContext; lpWildcard; lpfnMS; lpMS; dwWildcardSize; lpszMapDirectory; wCardNum; wCleanupAction; wScope; uUnitSize; bBurst; bFromRule; bSource; dwRecords; }; typedef stru
Chapter 4 - Implementing a Library EXIT Function Using the EXITPARAM Structure Component Used as Usage lpDataFromApp Output Data sent back to the server from the function uRetryCount Not used uRetryInterval Not used bRollback Not Used bCleanup Not used nReturn Output Return code based on the outcome of the function szErrMsg Output String message based on nReturn szFile Not used lpInternal Not used lpCmdStruct Not used lpAdaptParms Not used lpContext Not used lpWildcard Not u
Chapter 5 - Reserved Words and Symbols Reserved Words and Symbols Chapter 5 - Reserved Words and Symbols This chapter lists the words and symbols that have a special meaning. Reserved Words and Symbols The following is the list of reserved words and their intended use. Reserved Word or Symbol Intended Use $ Shorthand representation of the object name to the left of a rule cell. Used in a component rule or a map rule. : Used to separate components. :.
Chapter 5 - Reserved Words and Symbols Reserved Word or Symbol Reserved Words and Symbols Intended Use Function names Names of functions may be used as type names, but cannot be used as map names. For a complete list of functions, see Chapter 2 - Using Functions. Symbol abbreviations for entering non-printable characters. For a complete list of these reserved values, see Appendix A - Hex, Decimal, and Mercator Symbol Values. Operators Operators are reserved.
Chapter 5 - Reserved Words and Symbols Reserved Words and Symbols Reserved Word or Symbol Intended Use Separates type names. TRUE Represents the logical true. WHERE Can be used in the LOOKUP or EXTRACT function instead of the comma "," between arguments.
Appendix A - Hex, Decimal, and Mercator Symbol Values The table on the following pages lists the hexadecimal values, decimal values, and Mercator symbols abbreviation for the 128 ASCII characters. ♦ Hexadecimal values are provided as a reference for situations in which these values are used to specify syntax properties and pad characters in hex. ♦ Decimal values are provided as a reference for use with the SYMBOL function.
Appendix A - Hex, Decimal, and Mercator Symbol Values Reserved Words and Symbols Character Hex Value Decimal Value Mercator Symbol HorizTab 09 9 LineFeed 0A 10 VerticalTab 0B 11 FormFeed 0C 12 CarriageReturn 0D 13 ShiftOut 0E 14 ShiftIn 0F 15 DataLinkEscape 10 16 DeviceControl1 11 17 DeviceControl2 12 18 DeviceControl3 13 19 DeviceControl4 14 20 NegativeAcK 15 21 SYNchron.
Appendix A - Hex, Decimal, and Mercator Symbol Values Character Reserved Words and Symbols Hex Value Decimal Value Mercator Symbol # 23 35 – $ 24 36 – % 25 37 – & 26 38 – ' 27 39 – ( 28 40 – ) 29 41 – * 2A 42 – + 2B 43 – , 2C 44 – - 2D 45 – .
Appendix A - Hex, Decimal, and Mercator Symbol Values Character Reserved Words and Symbols Hex Value Decimal Value Mercator Symbol = 3D 61 – > 3E 62 – ? 3F 63 – @ 40 64 – A 41 65 – B 42 66 – C 43 67 – D 44 68 – E 45 69 – F 46 70 – G 47 71 – H 48 72 – I 49 73 – J 4A 74 – K 4B 75 – L 4C 76 – M 4D 77 – N 4E 78 – O 4F 79 – P 50 80 – Q 51 81 – R 52 82 – S 53 83 – T 54 84 – U 55 85 – V 56 86 – Functions a
Appendix A - Hex, Decimal, and Mercator Symbol Values Character Reserved Words and Symbols Hex Value Decimal Value Mercator Symbol W 57 87 – X 58 88 – Y 59 89 – Z 5A 90 – [ 5B 91 – \ 5C 92 – ] 5D 93 – ^ 5E 94 – _ 5F 95 – ` 60 96 – a 61 97 – b 62 98 – c 63 99 – d 64 100 – e 65 101 – f 66 102 – g 67 103 – h 68 104 – i 69 105 – J 6A 106 – k 6B 107 – l 6C 108 – m 6D 109 – n 6E 110 – o 6F 111 – p 70 112 –
Appendix A - Hex, Decimal, and Mercator Symbol Values Character Reserved Words and Symbols Hex Value Decimal Value Mercator Symbol q 71 113 – r 72 114 – s 73 115 – t 74 116 – u 75 117 – v 76 118 – w 77 119 – x 78 120 – y 79 121 – z 7A 122 – { 7B 123 – | 7C 124 – } 7D 125 – ~ 7E 126 – DELete 7F 127 – Functions and Exp ressions Referen ce Guide 239
Appendix B - Format Strings The tables on the following pages present the format strings for numbers, dates and times for use in functions such as the CURRENTDATETIME, FROMNUMBER, TONUMBER, FROMDATETIME, and TODATETIME. Date and Time Format Strings You can create custom date and time formats by using the following symbols based on the format strings described in the following sections.
Appendix B - Format Strings Date and Time Format Strings Date Units Symbol Description CCYY Full year including century (2001) YY Last two digits of the year (00-99) MM Month of the year in two numeric digits (01-12) M Month of the year in one or two numeric digits as needed (1 to 12) MON First three letters of the month (Jan to Dec) MONTH Full name of the month (January to December) DDD Day of the year in three numeric digits (001 to 366) DD Day of the month in two numeric digits (01 to
Appendix B - Format Strings Date and Time Format Strings Binary Date and Time Format Strings Sub-String Name Sub-String Value Binary DateTime [ "{" + Date + "}" ] + [ "{" + Time + "}" ] Date CCYYMMDD YYMMDD CCYYDDD YYDDD Time HH24MMSS HH24MM Japanese Date and Time Format Strings Sub-String Name Sub-String Value Japanese DateTime "{" + + "}" "{" + DateTime DateTime + "}" + "}" + Separator(1) + "{" + DateTime DateTime + "}" + "}" + "[" + + "]" Separator(1) + "{" + "{" + DateTime DateT
Appendix B - Format Strings Date and Time Format Strings Japanese Date Format Strings Sub-String Name Sub-String Value Japanese Date Year + MonthSet(1) Year CCYY YY EEYY EY MonthSet Separator(1) + "MM" + DayOfMonth(1) "[" + Separator(1) + "MM" + DayOfMonth(1) + ″]″ Separator(1) + ″M″ ″[″ + Separator(1) + ″M″ DayOfMonth + DayOfMonth(1) + DayOfMonth(1) + ″]″ Separator(1) + "DD" + WeekDay(1) "[" + Separator(1) + "DD" + WeekDay(1) + "]" Separator(1) + "D" "[" + Separator(1) + "D" WeekDay + WeekDay
Appendix B - Format Strings Date and Time Format Strings Japanese Time Format Strings Sub-String Name Sub-String Value Japanese Time Meridian(1) + HMS Meridian "AM/PM" HMS "HH24" + MinuteSet(1) "H24" + MinuteSet(1) "HH12" + MinuteSet(1) "H12" MinuteSet + MinuteSet(1) Separator(1) + "MM" + SecondSet(1) "[" + Separator(1) + "MM" + SecondSet(1) + "]" Separator(1) + "M" "[" + Separator(1) + "M" SecondSet + SecondSet(1) + SecondSet(1) + "]" Separator(1) + "SS" "[" + Separator(1) + "SS" + "]" Sepa
Appendix B - Format Strings Date and Time Format Strings Western Date and Time Format Strings Sub-String Name Sub-String Value Western DateTime "{" +DateTime + "}" "{" +DateTime + "}" + "}" Separator(1) + "{" + DateTime + "{" +DateTime + "}" + "[" + Separator(1) + "{" + DateTime + "}" + "]" DateTime Date Time Separator Separator is optional. If specified, it can be up to 120 bytes, composed of non-alphabetic characters.
Appendix B - Format Strings Date and Time Format Strings Sub-String Name Sub-String Value Separator Separator is optional. If specified, it can be up to 120 bytes, composed of non-alphabetic characters. Symbol table values, such as , can be used to indicate non-printable characters. When DateUnit has a variable length (M, MONTH, D, DAY), a Separator must follow that DateUnit if data follows.
Appendix B - Format Strings Sub-String Name Number Format Strings Sub-String Value "[AM/PM]" Zone "+/-ZZZZ" "ZZZ" "[+/-ZZZZ]" "[ZZZ]" Separator Separator is optional. If specified, it can be up to 120 bytes, composed of non-alphabetic characters. Symbol table values, such as , can be used to indicate non-printable characters.
Appendix B - Format Strings Number Format Strings Leading Sign Format Strings Sub-String Name Sub-String Value Positive Negativ e Leading-Sign "L" + Positive + Negative + Zero(1) Req Req Opt Req Req Opt Req — — Req -or- "L" + "[" + Positive + "]" + Negative + Zero(1) -or- "L" + Positive + "[" + Negative + "]" + Zero(1) -or- "L" + Positive + Zero(1) -or- "L" + Negative + Zero(1) Trailing Sign Format Strings Sub-String Name Sub-String Value Positive Negative Trailing-Sign "T" + Pos
Appendix B - Format Strings Number Format Strings Sub-String Format Strings Sub-String Name Sub-String Value Positive "+" + Value(1) Negative "-" + Value(1) Value A text-string enclosed in singlequotes '. Value has a release character of / if the text contains any single quote ' or forward slash / characters. The sign value of the previous sign-indicator. If Value is not used, the default is + for positive numbers and - for negative numbers.
Appendix B - Format Strings Number Format Strings Sub-String Name Sub-String Value Meaning Fraction "V" + ImpliedPlaces Specifies the decimal to be implicit and implied places specifies where the intended decimal separator is to be placed. - or – Value + MinDigits(1) + "##" + MaxDigits(1) - or - "[" + Value + MinDigits(1) + "##" + MaxDigits(1) + "]" Specifies the value of the decimal separator to be required and the range of fraction digits. If min-digits is not used, default is zero.
Appendix C - Return Codes and Error Messages The RUN function return codes and messages may result when using the RUN function. Return codes and messages are returned when the particular activity completes. Return codes and messages may also be recorded as specified in the audit logs, trace files, execution summary files, etc. Messages The following table lists the return codes and messages that can result when using the RUN function.
Appendix C - Return Codes and Error Messages Return Code Message 56 Invalid command line option –x The option is invalid for the command. 57 Invalid ‘W’ command line option The Work file option is invalid. 58 Invalid ‘B’ command line option The Batch (close) file option is invalid. 59 Invalid ‘R’ command line option The Refresh Rate option is invalid. 60 Invalid ‘A’ command line option The Audit option is invalid.
Appendix C - Return Codes and Error Messages Return Code Message 68 Invalid ‘O’ command line option for output x The target option is invalid for output x. The number of characters between the single quotes that represent the options for an adapter exceed 258 characters in the adapter override. 69 Invalid adapter type in command line for output x The adapter is not of a known adapter type. Includes –OMxxx where xxx is an unknown adapter alias.
Appendix C - Return Codes and Error Messages Return Code Message 77 Invalid map name $ This message can occur in two different cases. First, this message occurs when the map name specified on the command line is more than 32 characters long. Also, this message can occur when there is an error in the command line such that text for another execution command is erroneously being interpreted as the map name.
Index in an expression, 13 CHOOSE function, 72 CLONE function, 73 colon in a component path, 16 comment name, 19 comparison operators, 29 component in a component list, 16 in an expression, 16 component path, 13, 14 COMPONENT reserved word, 232 CONTAINSERRORS function, 74 CONVERT function, 76 COUNT function, 77 COUNTABS function, 78 COUNTSTRING function, 79 CURRENTDATE function, 80 CURRENTDATETIME function, 81 CURRENTTIME function, 83 $ $ in a component rule, 20 reserved symbol, 231 ( () reserved symbol,
Index F shorthand notation in, 19 EXTRACT function, 104 L LAST reserved word, 15, 232 LASTERRORCODE function, 137 LASTERRORMSG function, 139 LEAVEALPHA function, 141 LEAVEALPHANUM function, 142 LEAVENUM function, 143 LEAVEPRINT function, 144 LEFT function, 145 literals definition of, 10 in expressions, 10 local type name, 14 logical operators, 29 LOOKUP function, 146 LOWERCASE function, 148 F FAIL function, 105 FALSE reserved word, 232 FILLLEFT function, 107 FILLRIGHT function, 108 FIND function, 109 fl
Index P SETON function, 184 shorthand notation, 19 in a map rule, 21 SIZE function, 186 SORTDOWN function, 187 SORTUP function, 189 SQRT function, 190 SQUEEZE function, 191 STREAMTOHEXTEXT function, 192 SUBSTITUTE function, 194 SUM function, 195 SYMBOL function, 196 in expressions, 28 logical, 29 order of evaluation, 30 text, 28 OR function, 160 output items conversion of, 39 P PACK function, 161 PACKAGE function, 162 PARTITION function, 163 partitions in an expression, 14 PRESENT function, 164 PROPERCAS
