Creating Mapping Scripts

For Like mappings, you can create mapping scripts to designate conditional mapping. This type of mapping enables you to specify source members mapped to script expressions rather than to hard-coded target members. Target values are assigned in the script value. You activate the mapping script by placing #SQL in the Target value field for a SQL script. (The row that uses a script has target value of #SQL). Then the next row has the <!SCRIPT> delimiter and the actual script, followed by <!SCRIPT>. Mapping scripts, used in conjunction with dimension processing order, enables a mapping that is based on the results of dimension mappings. That is, dimension mappings that have already been processed. See Using Mapping Scripts.

To create a mapping script:

From the Workflow tab, select Data Load Mapping.
Optional: Select the desired location.
Select the Like tab.

Mapping scripts are unavailable for "Explicit" and "Multi-dimension" mapping types.
Select the Source Value.
In Target Value, select one of the following:
- For a Jython based mapping script, enter #SCRIPT.
- For a SQL based mapping script, enter #SQL.
  
  Note:
  
  For SQL based mappings, Oracle Hyperion Financial Data Quality Management, Enterprise Edition specifies the special value #SQL to the "Target Value." For this reason, this field cannot be used to specify a value of "IGNORE", which is otherwise used to set the VALID_FLAG to Yes or No. For SQL based mappings, you must include the VALID_FLAG in the SQL script and specify the value of "I" based on the user selected criteria.
In Rule Name, enter the data rule name for the mapping.
Click Save.

The Script Editor icon ( ) is enabled.
Click the Script Editor icon.
In Edit Script, enter the mapping script, and click OK.
For example the following Jython based mapping script checks when the first two characters of the account equals 11 and the entity equals 01. When a match occurs, then the target is set to Cash2 and the target amount is set to 0. In addition, the script also updates the attribute1 column in the TDATASEG table. (See TDATASEG Table Reference). This script uses the fdmResultMap object (see Using Jython Objects with Mapping Scripts).

In this example, SQL script is used logic is implemented in a SQL CASE statement. The CASE statement is used in the SET command of the internal UPDATE statement. The mapping statement below is converted to the UPDATE statement listed
```
UPDATE TDATASEG  
```
```
SET ACCOUNTX =
```
```
CASE
```
```
  WHEN ACCOUNT LIKE ‘L%’ AND ICP = ‘000’ THEN ‘A4140‘
```
```
  WHEN ACCOUNT IN (‘110’,’120’,’130’) THEN ‘A100’’
```
```
  ELSE ‘A’ || ACCOUNT
```
```
END
```
```
,DESC1 = COALESCE(DESC1, ACCOUNT || ‘.’ || ICP)
```
```
WHERE ACCOUNT …..
```