Need logic to implement

[sriec12]

I need guidance on the following issue.



I have a scenario like this.

From an oracle table A I will 80 million records. I need to check several conditions.

For example :

select * from TableA where CODE NOT IN ('A','B','C'........'F')
select * from TableA where CODE IN ('Z','X','Y')


I want to implement in Xtrm, is there any list operator "IN" like in datastage or should i use or condition like

if CODE <> 'A' or CODE <> 'B' .......... or CODE <> 'F' Then 'Y' else 'N'

Please guide me

[priyadarshikunal]

there is no IN function in transformer as such. However you can try to use index().

[sriec12]

what do u mean by index?

[chulett]

A couple of things.

if CODE <> 'A' or CODE <> 'B' .......... or CODE <> 'F' Then 'Y' else 'N'

Those would need to be ANDs to work, OR would always evaluate to TRUE.

Secondly, what you were given was not a 'workaround' but the resolution to your question and works just fine without any additional caveats as long as you're not doing substring matches - which you're not. The Index() function will return the position where the code was found, meaning a zero or FALSE response will mean it was not in the list.

However, I've reopened the topic since you obviosuly don''t have it working yet. Once you do you can come back and mark it Resolved.

[sriec12]

Thanks everyone for replying.

Well In my case I don't think Index works in my case.

Index will look for one substring Index (string, substring, instance)....I am trying to match upto 10 items.......If those 10 items matches I need to assign 'Y'. I am trying to find the function with matches a group of substrings

[chulett]

It will work just fine. Nothing in the examples you've posted shows you needing to match multiple items. For example:

Code: Select all

if CODE NOT IN ('A','B','C','D','E','F') then 'Y' else 'N'

Would equate to:

Code: Select all

If Index('ABCDEF',CODE,1) Then 'N' Else 'Y'

[priyadarshikunal]

what do u mean by index?

I don't know what "U", a member of this forum will tell about index.

Text-message abbreviations have a place, but it's not in forums. For answers to be effective, the recipients need to be able to understand the question. Many of the posters on this forum do not have English as a first language. Decrypting SMS-style abbreviations only makes their task of comprehension more difficult. You would not create system documentation using these abbreviations, please accord members here the same courtesy.

[priyadarshikunal]

As per the description and example given by you in the first post, index will work just fine. If you do not think this will work in your case, please describe with an example where you think this will fail.

[sriec12]

Thanks Chulett.

Its perfectly working, I used Index.

Once agian thanks everyone.

[priyadarshikunal]

Thats good to know.

Only place I have seen where this breaks is when one value is a substring of another value such as CODE IN ("ABCD","BC") in this case "BC" will have two matches with index function. Which will break the logic if you need to distinguish between ABCD and BC, if both need to have the same fate then you are good with that too.

In those cases as well you can use some character as prefix and suffix

such as "|":CODE:"|" IN ("|ABCD|","|BC|").
or in DataStage syntax

Code: Select all

If index("|ABCD|,|BC|","|":CODE:"|",1) then "N" else "Y"

[chulett]

Which is why I said that this "works just fine without any additional caveats as long as you're not doing substring matches". What you posted would be the shenanigans I was referring to.

[sriec12]

@priyadarshikunal: You Rocks !!!

You saved my time. Actually i have codes like 2 bytes : AB, CA, DE

after seeing your logic I got this idea and it works perfectly.

if Index('AB,BD,DE......EF ', trim(CODE),1) then 'N' else 'Y'

[chulett]

OK... that's still not a substring match and you're not quite right on your changes. The extra steps are necessary if you want to check for codes like "AT" or "MATCH" against a larger string, say like "MATCHES". Both of those would give false positive return codes as they are both found in the larger string. So there needs to be delimiters around all of the instances to search for and the value to search for as well. Technically, your way you would get a false positive hit on a CODE value of ",D" for example. Probably not one of your values, I know, but you still should be protecting yourself.

Code: Select all

if Index(',AB,BD,DE,......,EF,', ',':CODE:',',1) then 'N' else 'Y' 

Notice how there are now commas on both sides of each value and added around the CODE value as well for the check. This way you'd be hard pressed to get a false positive now.

ps. priyadarshikunal showed you the same thing with pipes around the values. Just trying to reinforce what they posted since you missed that key point.