Will there ever be an 'IN' clause for the Filter Stage

[bcarlson]

I have a filter criteria where we need to check if a column has one of many different values. The ideal would be to use an IN clause, as you would use in a database. Right now that option does not exist, do you think it ever will?

Is the intent of the Filter Stage to allow 'SQL-like' where clauses? If so, you would think an 'IN' clause would be an obvious and necessary addition.

For now, I am stuck with

Code: Select all

field1 = 'Y'
and (
    field2 = 'A'
    or field2 = '#'
    or field2 = 'C'
    or field2 = 'H'
    or field2 = 'K'
    or field2 = '2'
)

My actual filter is more complex, but you get the idea. I really like keeping the 'where' logic in one place, but sure wish it could be simplified. Any other suggestions? How have others done this?

Brad.

[kduke]

Why not pipe this to grep. A filter can be several commands or a shell script with this in it. grep can look for more than one thing by putting them in a file. You could easily use awk or sed or even Perl.

I am not sure about filter in PX. I was thinking about Server jobs.

[ray.wurlod]

Pressed for time would be my guess - "they" would have been applying immense pressure to push product out the door. Manuals can always be fixed in the next release.

[bcarlson]

There is an IN clause!!!! But like many things in parallel jobs it is not intuitive and it is not documented and it does not appear in any right click mouse menu. The LIKE operator can work as an IN operator by using a regular expression with the special characters [].

For example
Field2 LIKE "[A#CHK2]"

That is awesome!! I have 2 places in my filter that would benefit from an IN clause, but only one with work with this. Unfortunately, the other is a list of numerics. Oh well.

But thanks! I know of several other places where this will come in handy!

Brad.

[benny.lbs]

If it is not a one-character regular expression, how to code the LIKE clause ?

Code: Select all

field1 = 'Y' 
and ( 
    field2 = 'AB' 
    or field2 = '#C' 
    or field2 = 'CD' 
    or field2 = 'HE' 
    or field2 = 'KF' 
    or field2 = '2G' 
)

[vmcburney]

I was afraid someone was going to ask that question. Afraid I don't know. It should be standard C++ syntax, the Operator's Manual section came from Rogue Wave documentation. You could join the www.roguewave.com website and get some of the resources on C++ coding, they might have additional information about regular expressions in C++.

[mkkgupta]

Hi,

thanks for sharing about IN operator.
Is there any change to filter in new versions?

I am looking for IN operator with multiple characters using DS 7.5.1.

Here is an example of my requirement. I need to validate email host by looking around 30-40 hosts.
email_Host IN ('com','net','in','uk','org')


I am planning to use lookup by putting all those names in a file. is there any better alternative??

Thank you
Kiran

[anbu]

Code: Select all

If Index('com|net|in|uk|org',email_Host,1) > 0 Then 'Y' Else 'N'

[bcarlson]

That is for a transformer, right? The question was about the Filter stage.

[anbu]

That is for a transformer, right? The question was about the Filter stage.

Oops. I didnt read the question properly. Yes index is for transformer

[mkkgupta]

I created a new post as Brad marked it as resolved.
I am not sure shall I leave my other post as open or not!

it works for me however I am already using two transformers in a row to validate/filter email data. it will force me to use a third one or I need to figure out by adding some more Stg Var.

I thought it would be good by adding filter,which solves my problem as well as looks better.

thank you,
Kiran