DML functions: Table

Table functions

isKeyPresent / isKeyPresentInPeriod

returnColumn / returnColumnInPeriod

Table functions

Each column in a Table is defined by a name and a data class. Each line in a Table is accessible by its key and has a start date and an end date that enable you to define a validity period for the data in a line of the Table. For example, a Table could store monetary exchange rates that change over time. You can set a validity period for each exchange rate value listed in the Table and retrieve the values depending on the date that you specify.

If the validity periods for a given set of key columns overlap, the software takes into account the validity period with the most recent start date.

Table: Commission_Rates

The following Table, Commission Rates, contains commission rate information for transactions. The three Key columns, clearly indicated by the key icon Key icon, uniquely identify a line in the Table. The two Value columns (Rate and Minimum Commission Fee), identify the columns that contain the values you can retrieve. The Start Date and End Date columns identify a validity period for the data in each line of the Table.

Key icon
Transaction Type
Key icon
Source Country Code
Key icon
Destination Country Code
Rate Minimum Commission Fee Approved Start Date End Date

PTM

US

UK

0.10

100

True

20000601

20000731

PTM

US

UK

0.13

110

False

20000801

20000831

PTM

US

France

0.15

125

True

20000701

20000831

PTM

US

Germany

0.13

120

False

20000701

20000831

PTM

France

Germany

0.10

100

True

20000701

20000831

PTL

US

France

0.17

130

False

20000701

20000831

To access and retrieve the values stored in the Commission_Rates Table, use the following parameters:

  • table_name: Specifies the name of the Table.
  • If the name contains only digits or corresponds to a reserved-word, you must enclosed the name with the character !

    For example:

    returnColumn(!123!, "a")

    returnColumn(!do!, "a")

  • key_column_list: Lists all the Key column names. For example, "PTM", "US", and "France" uniquely identify the third line of the Commission_Rates Table as the line that contains the value that you want to retrieve.
  • value_column: Specifies the Value column in the Table that contains the value that you want to retrieve. For example, the Commission_Rates Table has two possible Value columns: MinimumCommissionFee and Rate. You must specify only one of these Value columns. If the Table has one Value column only, then this parameter is optional.
  • date_expression: Specify the validity period that applies to the specified line in the Table. For example, '2000-07-14' is part of the '2000-06-01' to '2000-07-31' period, specified in the first line of the Table.

isKeyPresent

isKeyPresent Comment

Description

isKeyPresent checks that a key is present in the Table. The Function returns true if it finds the line that corresponds to this key, and false if not.

Syntax

isKeyPresent(table_name, key-column-list)

Return

Boolean

Parameters

table_name
Enter the name of the Table.

key-column-list
Specify this parameter by using key_column_value. You must specify all the key columns in the Table in the correct order from left to right.

Example

In the following example, isKeyPresent checks the Commission_Rates Table for the line that includes the key values specified by the key_column_list parameter. If it does not find the line that contains the specified values, it applies the default commission rate of '0.20'. If the line is present in the Table, then the returnColumn Function is called to return the commission rate for that line:

if isKeyPresent(Commission_Rates, "PTM", "France", "US")

then returnColumn(Commission_Rates, "PTM", "France", "US", Rate)

else "0.20"

isKeyPresentInPeriod

isKeyPresentInPeriod Comment

Description

isKeyPresentInPeriod checks that a line is present in the Table for a given date. If the Function finds the line, it returns true, if not, it returns false. If the date is set to null or absent, the Function returns null.

Syntax

isKeyPresentInPeriod(table_name, key-column-list, date_expression)

Return

Boolean

Parameters

table_name
Enter the name of the Table.

key-column-list
Specify this parameter by key_column_value. You must specify all the Key columns in the Table in the correct order from left to right.

date_expression
Enter a Date, or a Date expression, that returns a Date. This parameter is mandatory and is used to identify the validity period in the Table that corresponds to the specified date.

Example

In the following example, isKeyPresentInPeriod checks that the line specified by the key_column_list parameter is present in the period specified by the date_expression parameter. If it does not find the line, then it applies the default commission rate of '0.20'. If the line is present in the Table and corresponds to the specified date, then the returnColumn Function returns the commission rate for that line:

if isKeyPresentInPeriod(Commission_Rates, "PTM", "France", "US", '2000-07-14')

then returnColumnInPeriod(Commission_Rates, "PTM", "France", "US", '2000-07-14', Rate)

else "0.20"

returnColumn

returnColumn Comment

Description

returnColumn searches for the line specified by the key-column-list parameter, and then returns the corresponding value specified by the value_column parameter. If returnColumn does not find the line using the specified key-column-list, it returns the value absent.

Syntax

returnColumn(table_name, key-column-list[,value_column])

Return

Data Class of the Value column

Parameters

table_name
Specify the name of the Table.

key-column-list
Specify this parameter by key_column_value. You must specify all the Key columns in the Table in the correct order from left to right.

value_column
Specify the Value column by its column name. You can only specify a single Value column. If the Table has only one Value column, this parameter is optional.

If the name of the column contains digits only, or corresponds to a reserved-word, you must enclose the name with the exclamation mark character "!".

Examples:

returnColumn(MyTable, "a", !for!)

returnColumn(MyTable, "a", !2!)

Examples

In the following example, using the Commission_Rates Table shown above, returnColumn returns the commission rate for a PTM transaction from the US to France.

returnColumn(Commission_Rates, "PTM", "US", "France", Rate) returns 0.15

In the following example, returnColumn returns the minimum commission fee for a PTM transaction from France to Germany.

returnColumn(Commission_Rates, "PTM", "France", "Germany", MinimumCommissionFee) returns 100.

returnColumnInPeriod

returnColumnInPeriod Comment

Description

returnColumnInPeriod returns the value of the Value column depending on the date specified. If the line is not found, returnColumnInPeriod returns the value absent. If the date is set to null or absent, the Function returns the value null.

Syntax

returnColumnInPeriod(table_name, key-column-list, date_expression[,value_column])

Return

Data Class of the Valuecolumn

Parameters

table_name
Specify the name of the Table.

key-column-list
Specify this parameter by key_column_value. You must specify all the Key columns in the Table in the correct order from left to right.

date_expression
Specify a Date, or an expression that returns a Date. This parameter is mandatory and is used to identify the validity period in the Table that corresponds to the specified date.

value_column
Specify the Value column by its column name. You can only specify a single Value column. If the Table has only one Value column, this parameter is optional.

If the name of the column contains only digits, or corresponds to a reserved-word, you must enclose the name with the exclamation mark character "!"

Examples:

returnColumn(MyTable, "a", !for!)

returnColumn(MyTable, "a", !2!)

Examples

In the following example, using the Commission_Rates Table shown in this topic, returnColumnInPeriod returns the commission rate that applies on the specified date, for a PTM transaction from the US to the UK:

returnColumnInPeriod( Commission_Rates, "PTM", "US", "UK", '2000-07-14', Rate) returns 0.10.

In the following example, returnColumnInPeriod returns the minimum commission fee that applies on the specified date, for a PTM transaction from the US to the UK:

returnColumnInPeriod(Commission_Rates, "PTM", "US", "UK", '2000-08-03', MinimumCommissionFee) returns 110.

Related Links