2 Replies Latest reply: Nov 9, 2011 4:21 AM by 868034 RSS

    How to create SQL IN filter using XQuery FLWOR

    868034
      Is it possible to create such FLWOR expression that will be pushed down to SQL using "SQL IN" filtering?

      Assuming following function declaration:

      declare function tns:filterOnLevels($levels as xs:double+) {  ... 

      I'd like to create FLWOR expression that will be pushed down to such SQL:
      select * from LEVELS where level IN ($levels)

      And executing tns:filterOnLevels(1,2,3) should execute statement with "where level IN (1,2,3)". SQL IN-like filtering have to be pushed down to SQL because executing unconstrained query could result with too many records.

      Is such expression possible to do in XQuery/XQSE or maybe other fn-bea extensions?

      I'm working on ODSI/Workshop 10.3 and Oracle 10g.
        • 1. Re: How to create SQL IN filter using XQuery FLWOR
          mikereiche
          ODSI uses multiple OR's instead of IN because (1) optimizers rewrite "IN" as multiple OR's anyway; and (2) OR is more general than "IN", since "IN" can only handle (a) equality; and (b) a single column, while multiple OR's can also handle inequality and multiple columns.

          In your example ...

          declare function tns:filterOnLevels($levels as xs:double*) {   (: use '*' instead of '+' here, the optimizer will thank you for it :)

          for $l in $levels
          for $LEVELS in LEVELS()
          where $LEVELS/LEVEL = $l
          return
          $LEVEL

          If you look at the generated query plan, it should contain ...

          select <all columns> from LEVELS where LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ? or LEVEL = ?


          http://download.oracle.com/docs/cd/E13162_01/odsi/docs10gr3/xquery/sql_pushdown.html#wp1134043

          Data Service Integrator (the Best Practices announcement).
          • 2. Re: How to create SQL IN filter using XQuery FLWOR
            868034
            Thanks for great explanation!
            The "SQL IN" scenario works like you described.