DSXchange

Posted: **Wed Aug 17, 2005 5:14 pm**

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:

Code: Select all

field1 = 'Y'
and field2 in ('A', '#', 'C', 'H', 'K', 'Z')

An IN clause should obviously be able to handle any normal character or numeric datatype, just like a database.

Brad.

Posted: **Thu Aug 18, 2005 10:21 am**

Allow me to add a clarification to my own posting. User vmcburney informed me of an adaptation of the LIKE clause that will help in my previous example:

Code: Select all

field1 = 'Y' 
and field2 LIKE [A#CHKZ]

However, a true IN clause would also handle numbers:

Code: Select all

field1 = 'N'
and field2 IN (100, 225, 237, 432, 786)

Brad.

Posted: **Fri Aug 19, 2005 2:49 am**

In server jobs (yes, I am aware Brad uses parallel jobs) there is a technique using a "range conversion" with Oconv. This returns the original string if it falls within one of the ranges in the second argument, or an empty string otherwise. To accomplise Brad's example you would use:

Code: Select all

Oconv(Argument,"R100,100;225,225;237,237;432,432;786,786")

I agree that an IN capability would be desirable in a parallel job Filter stage, particularly since the Information field says "an SQL-like constraint".

Posted: **Wed Jul 19, 2006 11:22 am**

Likewise there should also be NOT IN clause so that we donot have to check col1<>10 AND col1<>20 and so forth.

DSXchange

Adding an 'IN' clause to the Filter Stage's where condition

Adding an 'IN' clause to the Filter Stage's where condition