Suppose you have an input source—a database operation, for example—that returns a result set of transaction data. The prices in this data are in various currencies, and you need to express the prices in Euros. You have another input result set that has information about current Euro conversion rates.
The columns in the first input result set are:
ACCT_NO - the account number for the transaction
SYMBOL - the symbol of the stock purchased
NO_OF_SHARES - the number of shares of stock purchased
COST - the cost per share
CURRENCY - the currency in which COST is expressed
The columns in the second input result set are:
CURRENCY - the currency for which the conversion factor applies
MULTIPLIER - the factor by which the original value is multiplied to get the Euro value
Your goal is to produce a result set consisting of:
ACCT_NO - the account number for the transaction
SYMBOL - the symbol of the stock purchased
COST_IN_EURO - the cost in Euros of the transaction
One way to produce the result that you need (and probably the most straightforward way to use WorkSpace Data Federation) is to use a Join operator, employing the “Inner Loop Join” algorithm with the second input result set as the inner table. This produces a result set consisting of:
ACCT_NO - the account number for the transaction
SYMBOL - the symbol of the stock purchased
NO_OF_SHARES - the number of shares of stock purchased
COST - the cost per share
CURRENCY - the currency in which COST is expressed (from the first input)
CURRENCY - the currency for which the conversion factor applies (from the second input)
MULTIPLIER - the factor by which the original value is multiplied to get the Euro value
You can then use a Projection operator to remove the unneeded columns and to compute the COST_IN_EURO column (the formula being NO_OF_SHARES * COST * MULTIPLIER).
Solving the problem with a Custom operator
An alternative implementation for this same functionality is to use a Custom operator to build a lookup table that maps currency to multiplier and then performs the computation as you read in the account result set.
This example uses a .jsi file as well as a Custom operator. The .jsi file contains the following JavaScript:
var lookupTable = {}; // This will contain the mapping of currency // type to conversion factor. function setUpLookupTable(rs) { if (lookupTable.initialized == true) { return; // We only need to read the result set into the map once } while (rs.next()) { // Iterate over the rows in the result set var key = rs.getString(rs, "CURRENCY"); // Get currency type var value = rs.getRealValue("MULTIPLIER"); // Get conversion factor lookupTable[key] = value; // Save key/value pair } lookupTable.initialized = true; // So we don't try and do this again }
This code reads the “currency to exchange rate” result set into a hash table. The Custom operator has two inputs—one is called “LookupTableSource,” and the other is called “DataInputSource.”
The logic in the Custom operator itself is as follows (remember that this is a function with a Boolean return value—WorkSpace Data Federation supplies the outer braces, you only type the function body):
// Note that this function requires the lookup table input source // to be named "LookupTableSource" and the main input source to be named // "DataInputSource" setUpLookupTable(LookupTableSourceRS); // Initialize the lookup table // Check for more rows in DataInputSourceRS. If none remain, return false. // This also advances the row pointer to the DataInputSourceRS if (DataInputSourceRS.next() == false) { return false; } // Get the values of the fields we need from the input result set var accountNumber = DataInputSourceRS.getInt("ACCOUNT_NO"); var symbol = DataInputSourceRS.getString("SYMBOL"); var currencyType = DataInputSourceRS.getString("CURRENCY"); var numShares = DataInputSourceRS.getInt( "NO_OF_SHARES"); var costPerShare = DataInputSourceRS.getFloat("COST"); // Look up the Euro conversion factor for the original currency var conversionFactor = lookupTable[currencyType]; // Compute the value of the transaction in Euros var totalValue = numShares * costPerShare * conversionFactor; // Store the values in the output result set outputRS.updateInt("ACCOUNT_NO", accountNumber); outputRS.updateString("SYMBOL", symbol); outputRS.updateString("COST_IN_EURO", totalValue); return true; // Let the runtime know we provided another row
Send your feedback on this help topic to Sybase Technical Publications: pubs@sybase.com
Your comments will be sent to the technical publications staff at Sybase, Inc. For product-related issues or technical support, contact Sybase Technical Support at 1-800-8SYBASE.