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
IN / NOT IN functions
Moderators: chulett, rschirm, roy
You can use the INDEX() function, i.e.
will return a 0 if it isn't "in" the set or a positive integer if it is.
Code: Select all
INDEX('TER,RET,RWP,TWB,TWP,',r_tes.action:',',1)
-
- Premium Member
- Posts: 1044
- Joined: Wed Sep 29, 2004 3:30 am
- Location: Nottingham, UK
- Contact:
Code: Select all
INDEX('TER,RET,RWP,TWB,TWP,',r_tes.action:',',1)
Can you tell I used to be an assembly programmer?
Phil Hibbs | Capgemini
Technical Consultant
Technical Consultant
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.