IN / NOT IN functions

[hema177]

Is there any way we can use the IN function within Datastage? I have code which runs as follows

IF r_tes.action IN ('TER','RET','RWP','TWB','TWP') THEN v_reason_for_leaving := r_tes.action_reason; ELSIF r_tes.termination_dt = to_date(r_tes.snapshot_key,'YYYY/MM/DD') AND r_tes.empl_status NOT IN ('T','R') THEN ......

and so on for about two pages. I need to know whether these can be coded within datastage and if yes what would be the process?

Any suggestions welcome.

Thanks,

H

[ArndW]

You can use the INDEX() function, i.e.

Code: Select all

INDEX('TER,RET,RWP,TWB,TWP,',r_tes.action:',',1)

will return a 0 if it isn't "in" the set or a positive integer if it is.

[PhilHibbs]

Code: Select all

INDEX('TER,RET,RWP,TWB,TWP,',r_tes.action:',',1)

That's ok as long as you can identify a separator character that is guaranteed not to appear in your source data. I suppose the :',' also fixes this problem for fixed-length matches. I'm also a little uncomfortable with this from an efficiency point of view, as it is doing a lot of character comparisons. It needs to compare the first character of the search value with every character in the match list, so for a 3-character code like the example it is doing 4 times as many character comparisons as are strictly needed. However, it may be an acceptable approach as long as my first concern is not a problem. It's ideal for single-character matches like your "not in" clause.

Can you tell I used to be an assembly programmer?

[chulett]

Be aware it does a substring match. So if you are looking for 'ABC' in a list that includes 'ABCD' it will report a match / hit.

Check out the Locate function for an exact match.

[ray.wurlod]

Locate is a statement in DataStage BASIC, not a function that can be used in a parallel Transformer stage.

Modify stage has a useful lookup_int16_from_string() function if the list of values is fixed, which yours appears to be. It can be constructed to set a default value if the lookup fails.

Otherwise you have to construct the equivalent set of "=" conditions conjoined with Or operators, because there is no IN operator or function available in the Transformer stage.

Another alternative is the DIY alternative - create a BuildOp that performs this task.

[chulett]

D'oh! Lost track of where I was.