Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2006
    Posts
    2

    Question Unanswered: IN Clause syntax Error

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 --
    Code:
    where (
          RTS_ID           = -1
      and RTS_LINE_ITEM_ID = -1
          )
       or (
          RTS_ID           = -2
      and RTS_LINE_ITEM_ID =  3
          )
       or (
          RTS_ID           =  4
      and RTS_LINE_ITEM_ID =  5
          )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    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))

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2006
    Posts
    2
    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

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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 ...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Dec 2003
    Location
    North America
    Posts
    146
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •