2 Replies Latest reply: Aug 2, 2012 10:53 AM by Viksicom-Oracle RSS

    How to create VO with multiple dynamic where clauses on select with UNION?

    Viksicom-Oracle
      I am trying to implement the View Object for the UNION query that looks like this:

           select a,b,c...
                from t1,t2,...
                where dynamic_where_clause1     
           union all
           select a,b,c,...
                from t11,t12, ...
                where dynamic_where_clause2

      There are up to 60 input parameters that are used to generate dynamic where clauses. They are actually created by calling PL SQL function.

      So far, I was not able to assign both where clauses to the view object. Is there a workable solution for this problem, besides resorting to programmatic View Object?

      I understand that recommended way with UNIONs is to wrap both queries into a parent select:

      select * from (
           select a,b,c...
                from t1,t2,...
                where ... -- table relationship joints
           union all
           select a,b,c,...
                from t11,t12, ...
                where ... -- table relationship joints
      ) QRSLT
           where dynamic_where_clause

      Unfortunately this approach doesn't work here, since individual selects are producing unmanageable amount of data and resulting query takes forever to complete.