BODS doesn't allow using 'NOT IN' operator ?

[former_member214906]

Tried to use 'NOT IN' operator in query transformation where condition . Throwed a syntax error. Then figured out from the manual that we can't actually use NOT IN operator  but functionality of IN is available. Not sure about the challenges in using NOT IN. any idea?

[Former Member · Accepted solution]

Hi Srinivas,

Please try using NOT (Field IN (value1,value2)) in where.

Let me know if it helped.

Thanks,

Ancy

[Former Member]

How is it in the case of using not in with a column instead of fixed values.

NOT ORDER.CITY_ID IN (STOCK.CITY_ID)?

[Former Member]

Hi Chaithanya,

This can also be done.

Or try with NOT(ORDER.CITY_ID = STOCK.CITY_ID)

OR (ORDER.CITY_ID <> STOCK.CITY_ID)

Thanks

[former_member214906]

It worked for me Ancy. I did create three records in a table and used the following in where condition

TESTFORTO_CHAR_SRC.DATEYY in ('31-Sep-99', '31-Oct-96') - this returned me 2 records - expected.

For the same condition I just modified the condition as you said above

not (TESTFORTO_CHAR_SRC.DATEYY in ('31-Sep-99', '31-Oct-96')) - this returned me third record which is not in IN list.

Will try it on my actual requirement and verify if it is intended to give the expected results. Thank for the help!!!!