function equivalent to decode and add_months in data stage

linit · Post by **linit** » Sun Nov 12, 2006 12:08 pm

Hi,

Is there any function in data stage which is same as "DECODE" function in oracle? Similarly can anyone tell me the equivalent function of "ADD_MONTHS" in data stage.

narasimha · Post by **narasimha** » Sun Nov 12, 2006 12:53 pm

There is not DECODE function, but the same results can be got by many ways using Datastage
Do a search on DECODE and ADD_MONTHS, you will get your answers

These topics have been discussed many times here

ray.wurlod · Post by **ray.wurlod** » Sun Nov 12, 2006 1:00 pm

In the Modify stage (parallel jobs) there are lookup_string_from_int16() and lookup_int16_from_string() functions, plus equivalents for int32 and ustring data types. There is no equivalent in server jobs; most would choose to pre-load a small hashed file or to initialize a stage variable with a searchable list.

If you search I'm fairly certain you will find an AddMonths() server routine.

linit · Post by **linit** » Sun Nov 12, 2006 1:24 pm

Hi,

I would like to know how can we achieve the functionality of ADD MONTHS in data stage. For eg. I would like to explain the scenario.

I have a date col in my table and i have got another date which is static. I need to derive the previous 12 months information from the static date field and compare it with the DATE col.

IF DATE_NK=(ADD_MONTHS('PRD_MONTH'-1) THEN A ELSE 0

I want to achieve this...but i am not able to find out the equivalent of ADD_MONTHS. Can anyone tell me how to achieve the previous months information which i can compare with the DATE_NK.

ray.wurlod · Post by **ray.wurlod** » Sun Nov 12, 2006 4:34 pm

We are aware of what you want to do. We suggested search because, curiously, you are not the first to have this requirement. I'd be happy to do it for your for a fee, but the answer can be had for free with just a small amount of initiative.

Indeed, I have just checked, and the function has been posted.