#SCRIPT vs #SQL mapping

#SCRIPT vs #SQL mapping


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


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.




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


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.

Next article ODI Studio installation for FDMEE
Previous article EPM what's new?


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 *

seven + 6 =