NOT IN function in datastage
Moderators: chulett, rschirm, roy
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
NOT IN function in datastage
Hi All,
I am trying to code this in a stage variable in data stage version 8.1
if link.ID not in (40,50,100) then "do this" else if link.ID = link.ID then "do that"
else "remain where you are"
Can someone please let me know how do I accomplish this. I could not find any NOT IN function in etl.
please advise me.
Thanks
Mark
I am trying to code this in a stage variable in data stage version 8.1
if link.ID not in (40,50,100) then "do this" else if link.ID = link.ID then "do that"
else "remain where you are"
Can someone please let me know how do I accomplish this. I could not find any NOT IN function in etl.
please advise me.
Thanks
Mark
Set up a stage variable with that list in a dynamic array via its Initial Value. Then you could use a function like Locate() to search the list. You could do something similar with Index() as long as you avoid a substring match, typically handled by the proper use of delimiters.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
NOT IN function in datastage
Thank you. How do I create a dynamic array in ETL.
Thanks
Mark
Thanks
Mark
Here I would take a delimited string and then Convert the delimiters to Value Marks:
Pretty dang sure it should be Value Marks, can't test this right now.
Code: Select all
Convert(",",@VM,"40,50,100")
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
This is a parallel job, so linking to Locate() will be ugly.
I'd use a key-only Lookup stage, with the reference input fed by an External Source stage (echo 40,50,100). If the lookup fails then your NOT IN condition is satisfied. You can direct these rows to a reject link and have just those rows selected by that means.
I'd use a key-only Lookup stage, with the reference input fed by an External Source stage (echo 40,50,100). If the lookup fails then your NOT IN condition is satisfied. You can direct these rows to a reject link and have just those rows selected by that means.
Last edited by ray.wurlod on Mon Jan 18, 2010 3:52 pm, edited 1 time in total.
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.
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
NOT IN function in datastage
I am only see partial response. Rest of it is hidden. I am a premium member but still not able to read your complete response.
Thanks
Mark
Thanks
Mark
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
NOT IN function in datastage
Thank You.
I will send an email to mbership.director@dsxchange.com to find out the status of my membership
Thanks
Mark
I will send an email to mbership.director@dsxchange.com to find out the status of my membership
Thanks
Mark
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact: