Currency Function

Use the Currency Function to convert a currency value in a source column from one currency to another. The source column must be defined as numeric, but not floating point. Two conversion methods are available:

Direct Conversion: Provide conversion parameters based on values defined in a Currency Definition. Use the Currency Function to convert a monetary value in a column (replacing the prior value) or, by defining different source and destination columns, retain the original value and the converted value. You can explicitly define the source and destination currency types or you can identify a reference column to indicate the currency type.
The first calculation preference is to use the conversion rate for the source currency to the destination currency. The second calculation conversion preference is to use the conversion rate for the destination currency to the source.
Triangulation: Convert the value in a column from the source currency to the euro dollar and then convert the euro dollar to the destination currency. Both rates must be provided in the rate table: one between the euro dollar and the source currency, and one between the euro dollar and the destination currency. The specification expression is TRIANG or TR.

The Currency Function is formatted as follows:

CURRENCY( {ST=code | SS=(column-name,Types Table number)}
 {DT=code | DS=(column-name,Types Table number)}
 [SC=column-name] [TR] [CU=Currency Definition name]
 [TD=transaction-date-column-name] [DF='format']
 [NS=scale] )

The Currency Function must include at least a combination of the source currency type (ST) or source specification (SS) and the destination currency type (DT) or destination specification (DS). All other parameters are optional.

The source and destination currency types can be specified in one of two ways:

Use the ST/DT keywords to allow explicit specification of the currency using the three-character ISO 4217 Currency Code. The code is searched for on the Rates tab of the Currency Definition (see Rates Tab).
Use the SS/DS keywords to allow indirect specification of the currency type where a value in a named column in the row is used as a key. The key is correlated with a currency type as defined in the specified Currency Definition Type Table.

If you specify a transaction date (TD) and the transaction date column does not use the DATE format, you must also specify a date format (DF). If a specified transaction date is outside the date ranges specified in the Currency Definition Rates Table, the nearest date range is used for conversion calculations.

Note: If any required data (for example, currency types, rates) are missing, conversion errors will result at run time.

The following table describes the valid format and allowed values for the Currency Function parameters. Parameters can be specified in any order. Use commas or spaces to separate parameters in the Currency Function.

Parameter	Format
Source Column	SC=column-name SRCCOL=column name
Source Currency Type	ST=code SRCTYP=code where code = ISO 4217 Currency Code
Destination Currency Type	DT=code DSTTYP=code where code = ISO 4217 Currency Code
Source Specification	SS=column name, Types Table number SRCSPC=column name, Types Table number where column name, Types Table number = Types Table column and number (defined in the Currency Definition) to be used to specify the source currency type
Destination Specification	DS=column name, Types Table number DSTSPC=column name, Types Table number where column name, Types Table number = Types Table column and number (defined in the Currency Definition) to be used to specify the destination currency type
Triangulation (Forces conversion via the Euro dollar)	TR TRIANG
Currency Definition	CU=Currency Definition name CURTBL=Currency Definition name where Currency Definition name = Currency Definition that contains the appropriate conversion parameters. (Overrides the Currency Definition specified in the Insert request. See Using the Currency Editor.)
Transaction Date	TD=column name TRNDAT=column name where column name = Transaction Date column name to identify the conversion date
Date Format	DF='format' DATFMT='format' where format* = format of transaction date column, if not Date type.*
Numeric Scale	NS=scale NUMSCL=scale where scale = scale to be applied to Oracle numeric destination columns with an undefined scale.

Example 1

To convert from Finnish Markkas to Euro Dollars, format the Currency Function as:

CURRENCY(ST=FIM DT=EUR)

If the original value must be preserved, use the Currency Function to provide a value for a different column in the destination table.

Example 2

To convert from Finnish Markkas to Euro Dollars, and create a new column to retain the original source value (in Finnish Markkas) in a column labeled ITEM_COST, format the Currency Function as:

CURRENCY(ST=FIM DT=EUR SC=ITEM_COST)

Example 3

The preceding examples assume a simple conversion from a known and fixed source currency to a target currency. Consider a more complex scenario where a column in a table has a numeric monetary value, and a separate column in the table has a key to indicate the type of currency. The monetary value in one row could be Euro Dollars and in another row it could be Finnish Markkas.

To support this scenario, the Types tab of the Currency Definition is used to create a Types Table. (See Types Tab.) The Types Table defines the key and the corresponding currency type for the key. The Currency Function must include a source specification that identifies the column that contains the key, and the number of the Types Table that defines the key, as shown:

CURRENCY(SC=ITEM_COST SS=(ITEM_BASIS,1) DT=EUR)

SC=ITEM_COST: The source value is in the column ITEM_COST.
SS=(ITEM_BASIS,1): The key for the type of currency is in the column ITEM_BASIS, and Types Table 1 in the Currency Definition is used to correlate the key to the currency type.
DT=EUR: The result of the calculation displays as euro dollars.