NOT IN function in datastage

[pavan_test]

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

[chulett]

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.

[pavan_test]

Thank you. How do I create a dynamic array in ETL.

Thanks
Mark

[chulett]

Here I would take a delimited string and then Convert the delimiters to Value Marks:

Code: Select all

Convert(",",@VM,"40,50,100")

Pretty dang sure it should be Value Marks, can't test this right now.

[ray.wurlod]

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.

[chulett]

D'oh. Dang PX job.

[pavan_test]

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

[chulett]

Mark, you might want to double-check on your Premium Membership status as it doesn't show you as one - there's an indicator by your name on every post that should show that and it's missing from yours.

[pavan_test]

Thank You.

I will send an email to mbership.director@dsxchange.com to find out the status of my membership

Thanks
Mark

[ray.wurlod]

Meanwhile I've opened the status of the reply. Not much of it was hidden in any case.

[chulett]

And now it shows your status as a Premium Member... can you see everything?

[ray.wurlod]

You left the first "me" off "membership".