1 Reply Latest reply: Jul 10, 2011 11:17 PM by Mikereiche-Oracle RSS

    in operator

    867085
      Hi All,

      is there any way through which i can get below result for in operator?

      select * from transaction where tr_id in (101,101,102)

      i want 101 transaction to be appeared twice in a result..

      thanks in advance
        • 1. Re: in operator
          Mikereiche-Oracle
          is there any way through which i can get below result for in operator?
          select * from transaction where tr_id in (101,101,102)
          ODSI uses "OR" instead of "IN" - the database optimizer reduces both to the same plan. The benefit of using OR is that it will work on multiple columns.
          i want 101 transaction to be appeared twice in a result
          The database will only return 101 once for the sql that you have shown.

          That said, the following xquery should return what you want :

          for $tr_id in (101, 101, 102)
          for $trans in transaction()
          where $trans/tr_id eq $tr_id
          return trans

          If that doesn't give what you want, you can get ODSI to execute ...


          select * from transaction where tr_id=? // 101
          select * from transaction where tr_id=? // 101
          select * from transaction where tr_id=? // 102

          by using the fence function as below. The fence function simply blocks optimization.

          for $tr_id in (101, 101, 102)
          fn-bea:fence(
          for $trans in TRANSACTION()
          where $trans/tr_id = $tr_id
          return
          $trans
          )