DS equivalent of DECODE function of Oracle

[rxs0005]

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 )

[kcbland]

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:

Code: Select all

CONVERT "ABC" TO "123" IN yourstring

This would change all occurrences of "A" to a "1", "B" to a "2", and "C" to a "3".

So,

Code: Select all

CONVERT "ABC" TO "123" IN "CxBxA"

produces

Code: Select all

3x2x1

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.

[rxs0005]

Kc

thanks i had a Q regarding CONVERT instead of hard coded values i can substitute a column name form the same table right for eg:

update table
set col_X = decode(col_x, null, col_Y , 0, col_Y, col_X )

thanks

R

[kcbland]

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.

[kduke]

Why not if then else.