Hi all,

I am new to informix and I face a small issue. The following query throws 'syntax error'

select RTS_ID,RTS_LINE_ITEM_ID,RTS_LINE_STATUS from RTS_LINE_ITEM where (RTS_ID,RTS_LINE_ITEM_ID) in ((-1,-1),(-2,3),(4,5))

But this is completly valid is Oracle and with a minor modification works for DB2. I need to know what is the exact syntax for making it work for informix.

thanks a lot people,
Venkatesh

well, i dunno about the spiffy row constructors, and whether informix supports them or not, but you could always do it the old school way

change this --

where (RTS_ID,RTS_LINE_ITEM_ID) in ((-1,-1),(-2,3),(4,5))

to this --

I think you'll find success if you eliminate some of the embedded parentheses and set the WHERE conditions separately for each item:

select RTS_ID, RTS_LINE_ITEM_ID, RTS_LINE_STATUS
from RTS_LINE_ITEM
where (RTS_ID in (-1,-2,3,4,5) or RTS_LINE_ITEM_ID in (-1,-2,3,4,5))

what???

i think you'll find success if you search only for the same values presented in the original question

this --

... where (RTS_ID in (-1,-2,3,4,5) or RTS_LINE_ITEM_ID in (-1,-2,3,4,5))

will allow 25 different pairs of values, whereas the original question allowed only three

can you see the difference?

yeah r937 is correct. we have a combination search and we cannot split the parenthesis.

my problem is i am using a for loop which populates the right hand side of the IN Clause, and that loop is common for all database servers. (Oracle and DB2). If i dont get an similar syntax in informix for the query,i have to write a separate logic for this DB and it will create redundant code. I dont know whether informix supports this, but lets wait some more time

bite the bullet and fork your code

if you are thinking that you can use the same code for all dbms systems, that's a myth

best example: get last three rows based on a datetime column

SQL Server uses TOP, DB2 uses FIRST, Mysql uses LIMIT, Oracle uses ROWNUM ...

Opps, my bad.

I'm not familiar with Oracle or DB2 syntax & I've never seen Informix use syntax like (RTS_ID,RTS_LINE_ITEM_ID) in ((-1,-1),(-2,3),(4,5)) where the value of the 1st position (RTS_ID) is compared only to the 1st value in the 3 pairs of values (-1, -2, 5) and so on.