Converting "value IN (1,2,3,4,5,6,7) from Oracle to tra

[boris_karloff]

Hi there,

I have tried searching on this but as many things seem to be named dirrefently in DS to Oracle I have had to post.

I would like to know the way of defining and IN list predicate in a transformer.

i.e. Oracle code ....... FIlter where

value IN (1,2,3,4,5,6,7,8,9)

Can some one please shed some light on this please.

Thanks,
B

[ray.wurlod]

There is no IN operator in a Transformer stage. You could - in this particular case - use an Index() function.

Code: Select all

Index("1234567",InLink.TheValue,1)

which returns 0 if the value is not in the string of digits.

[boris_karloff]

Thanks Ray,

I may be asking a little too much but....

How would you implement if valueA is in (1,2,3,4,5,6,7,8,9) by fitlering reuslts in a filter stage or transformer, without writing...


To_Transform.ValeA = '1' Or
To_Transform.ValeA = '2' Or
To_Transform.ValeA = '3' Or
To_Transform.ValeA = '4' Or
To_Transform.ValeA = '5' Or etc........

Thanks,
B

[hamzaqk]

Well doh ? Ray just put the code on how to do it

Lets try again:

Code: Select all

INDEX("1,2,3,4,5,6,7,8,9", To_Transform.ValeA ,1)

Put this is the transformer constraint. The fist parameter depends on your "etc".

[boris_karloff]

hamzaqk i don't think it is "Doh", Ray said this will return 0 if the string does not match. I therefore thought that it would return 1 if it did match.

All too often on this forum people assume the inital posters understanding of DS should be excellent, and lets face it if we are posting questions that we are not very sure of then I guess that points to the fact we are not.

I does sadden me that because of this I am very reluctant to even post agin on this site due to these types of remarks I see and read here from other users replys.

B

[chulett]

I haven't seen any such assumption about people's understanding, if anything it's usually the other way around. I for one will click on a user's name to see how they have ranked their own knowledge of the product and use that to craft an answer. No guarantees if someone has been overly generous in their self ranking.

However, that doesn't mean you can always expect total hand-holding and unfortunately some people come here expecting a handout. We answer when we can and have our own jobs to do as well as help people here gratis. And sometimes it's more about guidance, critical thinking and problem solving 'nudges' so people end up solving their own problem and learning from the experience rather than the Silver Platter approach. Plus there is an assumption that people will take advantage of the online help and pdf manuals to look up functions or items referenced here without being specifically told to do so.

None of this is directed specifically at anyone, let alone you. There should be zero reluctance on your part to come back here for help - you'll find it an indispensable source for timely and complete assistance for your DataStage woes. However, please don't think (as some people tend to do) that we are a substitute for training or basic computer knowledge. There are other resources for that kind of help.

Well... after all that, the online help for the Index function would reveal that it actually returns the position of the found string, and zero when it can't find it. You can take advantage of the fact that, as a Boolean, zero is false and anything else is considered true. Which is why the IN equivalent posted assumed all you needed was a Boolean result as the IN returns. So, for your second question (which still has the same answer as the first) your derivation code could look like:

Code: Select all

If Index("1234567",To_Transform.ValeA,1) Then <true path> Else <false path>

As a constraint all that is needed is the Index function without any if-then-else wrapper.

Hope that helps! :D

[kcbland]

boris, sorry you think so poorly of your forum. I would like to point out something here:

You posted a question at 5:34am Dallas time. 21 minutes later you had a concise and perfect solution posted by Ray that needed no further clarification. He gave you the exact statement to use.

5 minutes later you followed up asking a question that clearly showed you did not do any further research on your own, such as investigate the INDEX() function completely. Ray neglected to followup (and using the RMM stage I would venture to guess that he would give you time to do your own investigation and expand your knowledge completely.

51 minutes later a non-Premium Poster stated you had your answer completely and utterly and use it as-is.

50 minutes later you followup with your complaint against the forum. Stating your reluctance to post again, you clearly indicate your dissatisfaction.

I've now spent more time typing this response than I spend on most responses. Do you realize how many posts flow thru this site in a day? As Premium Posters we cannot answer paragraphs for every post and hope that posters, especially Premium Members, can take the information we impart and use that as the starting point to get their solution in place. In your case here, you were given the entire solution. I'm trying to figure out your dissatisfaction.

If you have an issue with another posters response, then respond to them.

[kcbland]

It's a free forum within the discretion of your Editor. Lively debate and conversation entertains and enlightens everyone. If folks need to express emotions with their posts, use the Emoticons. If you don't like someone's post, use this icon, the rolling of the eyes:

Well doh ? Ray just put the code on how to do it

Lets try again:

Code: Select all

INDEX("1,2,3,4,5,6,7,8,9", To_Transform.ValeA ,1)

Put this is the transformer constraint. The fist parameter depends on your "etc".

[kcbland]

Also notice that my previous reply is Premium Content only, so only you can see the full response. See, we value our Premium Members.