Not In clause in Filter stage...

[kaps]

How do we implement SQL "NOT IN" logic in Filter stage.

I have following condition in a filter stage which does not output the expected number of records.

(Field1 <> Val1 Or Field1 <> Val2 Or Field1 <> Val3) and Field2 <= Val4

But If I use the following in the transformer it gives the correct number of records as output.

Not (Field1 = Val1 Or Field1 = Val2 Or Field1 = Val3) and Field2 <= Val4

What am I missing here ?

Thanks for your time...

[adityavinay]

Use this syntax (use AND instead of OR)
(Field1 <> Val1 and Field1 <> Val2 and Field1 <> Val3) and Field2 <= Val4

[kaps]

I have already tried that and it did not work...I remember in DataStage you have to use OR for NOT IN conditions...

[Kryt0n]

The solution given is identical to your second sample that you said works. But if it works, why a problem, just keep using it.

Your first sample is wrong because Field1 will never equal all three values at the same time therefore two of the three checks will always be true

[kaps]

I am trying to see if there is a way we can implement NOT IN logic in FILTER stage. I can use the 2nd method of doing it in the transformer but wondering what's wrong with the 1st one. Does filter stage not support NOT IN ? I understand that all the conditions won't be true for one record and thats why I gave OR so it works across multiple records.

Let me know if I am wrong.

[ray.wurlod]

The following list summarizes the Boolean expressions that are supported. In the list, BOOLEAN denotes
any Boolean expression.
- true
- false
- six comparison operators: =, <>, <, >, <=, >=
- is null
- is not null
- like 'abc' (the second operand must be a regular expression)
- between (for example, A between B and C is equivalent to B <= A and A> = C)
- not BOOLEAN
- BOOLEAN is true
- BOOLEAN is false
- BOOLEAN is not true
- BOOLEAN is not false
Any of these can be combined using AND or OR.

No IN and no NOT IN.

You might contemplate using a Data Rules stage if you're running version 8.7 and have Information Analyzer licensed. It does support IN, NOT IN, MATCHES regular_expression and lots of others.

[adityavinay]

I have already tried that and it did not work...I remember in DataStage you have to use OR for NOT IN conditions...

I used that syntax(AND) , it is working.

input
col1 , col2, col3
1,10,2
2,20,2
3,30,2
4,40,4

filter condition

col2 <> 10 And col2 <> 20 And Col3 <=2

output
col1 , col2, col3
3,30,2

[kaps]

AND works but the problem is if there are Null values in the field being cheked then we have to explicitly specify that where as in SQL it takes care of it implicitly.

Thanks to all...

[chulett]

I'm curious who's version of SQL you're working with that takes care of NULL comparisons "implicitly"?