Page 1 of 1
Not In clause in Filter stage...
Posted: Wed Jul 25, 2012 12:30 pm
by 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...
Re: Not In clause in Filter stage...
Posted: Wed Jul 25, 2012 12:44 pm
by adityavinay
Use this syntax (use AND instead of OR)
(Field1 <> Val1 and Field1 <> Val2 and Field1 <> Val3) and Field2 <= Val4
Posted: Wed Jul 25, 2012 4:18 pm
by kaps
I have already tried that and it did not work...I remember in DataStage you have to use OR for NOT IN conditions...
Posted: Wed Jul 25, 2012 4:40 pm
by 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
Posted: Wed Jul 25, 2012 10:00 pm
by 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.
Posted: Wed Jul 25, 2012 10:23 pm
by ray.wurlod
Parallel Job Developer's Guide wrote: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.
Posted: Thu Jul 26, 2012 7:55 am
by adityavinay
kaps wrote: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
Posted: Fri Jul 27, 2012 8:18 am
by 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...
Posted: Fri Jul 27, 2012 8:40 am
by chulett
I'm curious who's version of SQL you're working with that takes care of NULL comparisons "implicitly"?
