FDMEE

#SCRIPT vs #SQL mapping

#SCRIPT vs #SQL mapping

Introduction

One of the most recurrent problem when loading data is to map accounts depending on the sign of the amount.

Solution

There’s two simple ways to deal with that: Jython scripting or SQL scripting.
In cloud version of FDMEE (Data Management), Jython scripting is not allowed so SQL scripting is mandatory.

 

Examples:

Jython

In a “in”, “between” or “like” mapping, use the #SCRIPT statement.

 

 

The first line retrieve AMOUNT

montant = float(fdmRow.getString(“AMOUNT”)), don’t forget to cast in float otherwise there’s no error but a test on a string (as fdmRow.getString gets a string) gives unpredictable results !
Then a test on the sign:

if  montant > 0: if negative the amount sign is change

fdmResultMap[“AMOUNTX”] =  montant * -1

SQL

In the same way, in a “in”, “between” or “like” mapping, use the #SQL statement.

 

 

Here the line is directly converted during query in database.

There’s 2 statements, one for the mapping itself : CASE WHEN AMOUNT > 0 then ‘A4600’ ELSE ‘L40100’ END

and a second to change sign: ,AMOUNTX=CASE WHEN AMOUNT <0 THEN AMOUNT*-1 ELSE AMOUNT END.

Previous article EPM 11.1.2.4.900: what's new?

0 Comments

No Comments Yet!

You can be first to comment this post!

Leave a Comment

Your data will be safe! Your e-mail address will not be published. Also other data will not be shared with third person. Required fields marked as *

14 − 12 =