CAN WE USE DECODE FUNCTION

ravindrag · Post by **ravindrag** » Thu Oct 12, 2006 3:46 am

hi

i want to know can we use decode function in datastage . are any other functions similar to decode

ArndW · Post by **ArndW** » Thu Oct 12, 2006 3:48 am

There are hundreds of different "decode" functions out there in the programming world. Which one are you referring to?

ravindrag · Post by **ravindrag** » Thu Oct 12, 2006 3:50 am

i want to decode for a column like below

DECODE ( CONST_NAME
'Five', 5,
'six', 6,
'seven', 7,
'ten', )

ArndW · Post by **ArndW** » Thu Oct 12, 2006 3:58 am

There is no DECODE function like that built into DataStage. There are various ways of doing this starting with a long IF-THEN-ELSE or a CASE statement. How you solve this depends upon what your primary objectives are - simplicity or performance.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 12, 2006 4:16 am

In a server job the most efficient mechanism is to use a Hashed File stage servicing the reference input link of a Transformer stage.

ArndW · Post by **ArndW** » Thu Oct 12, 2006 6:48 am

Ray - not for a DECODE with only 4 values; that would be a lot of overhead. an IF-THEN-ELSE... construct or {my preference} a INDEX/LOCATE statement is going to be faster than a cached hash lookup. If there are tens or hundreds of lookups then that would be a different matter.

chulett · Post by **chulett** » Thu Oct 12, 2006 6:55 am

Hmm... as with most things, 'depends'. Another consideration would be the nature of the decoded data. If you have a static, fixed, never will change list then you've covered all the considerations. If there is a possibility that it could change, I'd look into storing the decode data in a flat file and sourcing it into a cached hashed file in the job each run. That way you don't have to touch the job to add a new decode value pair. And unless you are doing a bajillion lookups, I wouldn't be too concerned about the 'overhead' it adds to the job.

ray.wurlod · Post by **ray.wurlod** » Thu Oct 12, 2006 1:44 pm

I'd still use a hashed file, even for four rows. Might even populate it with an INSERT statement in ExecTCL before-stage subroutine in this case.