hi all,
I need to do an transformation based on DECODE logic i use in oracle
in the TRANSFORMER which fucntion / routine i use
thanks
eg:
update table
set col_X = decode(col_x, null, ABC, 0, ABC, col_X )
DS equivalent of DECODE function of Oracle
Moderators: chulett, rschirm, roy
You can always use the CONVERT statement , which is rather cool. It takes a string of characters and changes the each value to a correlated value in another string.
Example:
This would change all occurrences of "A" to a "1", "B" to a "2", and "C" to a "3".
So,
produces
Now, if you have wordsinstead of single characters, you're going to have to write your own decode function. There is no word-level matching. You'll have to pass a delimited string of search values and their replacement values to the function. You can use smart array logic to LOCATE (look it up) the search value and then return the correlated result.
Example:
Code: Select all
CONVERT "ABC" TO "123" IN yourstring
So,
Code: Select all
CONVERT "ABC" TO "123" IN "CxBxA"
Code: Select all
3x2x1
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
What do you mean, column name? Are you talking about during the actual SQL execution? It's too late. If you need the before-image of a row you need to update, use a reference lookup of some fashion and make the new data and the current data available in the transformer in order to do the logic you desire.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle