8 Replies Latest reply: Jan 11, 2013 5:21 AM by Timo Hahn RSS

    Other Bind Variables is not applied

    Neliel-Oracle-Newbie-Oracle
      Hi,

      I have a view object with the following query. I have one bind variable in query
      select 
      .   /* More Select Statements and Joins Code */
      .
      where
      .  /* More Filter Criteria Code */
      .
      and a.code = :Code
      ..and I have defined two bind variables also. One is required and the other is optional.
      Code ---> Required
      Group --> Non-Required
      In an application module code, I have created this custom method to query the program to filter the data according to a specific
      code and group
      public RowSetIterator getPartsGrouping(String code, String group)
      {
           PartViewImpl partsView =  getPartsViews();
           partsView.setCode(code);
           partsView.setGroup(group);
           partsView.executeQuery();
           return partsView.getRowSetIterator();
      }
      The query returns all data that pertains to a specific code but it does not filter the data that belongs to a specific group.

      I am not sure but did I miss something? It seems that my group variable is ignored.

      Did I miss something?

      JDEV 11G PS 5
        • 1. Re: Other Bind Variables is not applied
          Timo Hahn
          I don't see the other bind variable anywhere in the query.
          I assume that you did not add it there (e.g. as view criteria), which means that it is simply ignored when you execute the query.

          Timo
          • 2. Re: Other Bind Variables is not applied
            Neliel-Oracle-Newbie-Oracle
            Timo Hahn wrote:
            I don't see the other bind variable anywhere in the query.
            I assume that you did not add it there (e.g. as view criteria), which means that it is simply ignored when you execute the query.

            Timo
            Hi Timo,

            Yes, the other bind variable(:group) is not included in the query and I did that intentionally.
            Since this is a custom application module method, this is where I would like to filter the view with both
            code and group.

            I have other method that uses only the code.

            Is this not possible?

            Thanks
            • 3. Re: Other Bind Variables is not applied
              Arunkumar Ramamoorthy-Oracle
              Hi,

              Since the bind variable you are trying to set does not used anywhere in the query, it does not make sense to set it from code. Instead, you can add a default value for the bind variable, and use it in your query and then apply the proper value through code.

              Ex:
              select 
              .   /* More Select Statements and Joins Code */
              .
              where
              .  /* More Filter Criteria Code */
              .
              and a.code = :Code
              and a.group like '%'||:Group||'%'
              -Arun
              • 4. Re: Other Bind Variables is not applied
                Neliel-Oracle-Newbie-Oracle
                Arunkumar Ramamoorthy wrote:
                Hi,

                Since the bind variable you are trying to set does not used anywhere in the query, it does not make sense to set it from code. Instead, you can add a default value for the bind variable, and use it in your query and then apply the proper value through code.

                Ex:
                select 
                .   /* More Select Statements and Joins Code */
                .
                where
                .  /* More Filter Criteria Code */
                .
                and a.code = :Code
                and a.group like '%'||:Group||'%'
                -Arun
                Hi,

                I thought it's possible to set the where clause of the view object programmatically at runtime?
                For this particular view object, the only required filter for the bind variable is the :Code. There are application module that
                supplies only the code bind variable.

                As I am making another custom method, I am in need of filtering this view object with the :Group bind variable so I added it
                and then set it as not required.
                I am thinking that in my application module custom method, I could supply the value of both :Code and :Group at runtime.

                I did not bother creating a new View Object with both bind variable(:Group and :Code) explicitly defined as named bind variable in the query itself
                as I thought that I could set the value of the bind variables at runtime.

                Sorry if I might be confused as I am not that familiar with ADFBC....
                • 5. Re: Other Bind Variables is not applied
                  Timo Hahn
                  @Arun this is not recomended as this way the query always contain the additional query. The use case says it only needed in this method. Using "'%' || :bindVar || '%'" is a performance killer for big tables and should be avoided.

                  @Neliel
                  You can create a ViewCriteria in the VO and add this when the method is called from code.
                  ...
                          ViewCriteriaManager lCriteriaManager = partsView.getViewCriteriaManager();
                          partsView.setGroup(group);
                          ViewCriteria lCriteria = partsView.getViewCriteria("YOUR_VC_NAME");
                          partsView.applyViewCriteria(lCriteria);
                          partsView.executeQuery();
                  ...
                  (Code not tested: done from memory :))

                  Timo
                  • 7. Re: Other Bind Variables is not applied
                    Neliel-Oracle-Newbie-Oracle
                    Timo Hahn wrote:
                    @Arun this is not recomended as this way the query always contain the additional query. The use case says it only needed in this method. Using "'%' || :bindVar || '%'" is a performance killer for big tables and should be avoided.

                    @Neliel
                    You can create a ViewCriteria in the VO and add this when the method is called from code.
                    ...
                    ViewCriteriaManager lCriteriaManager = partsView.getViewCriteriaManager();
                    partsView.setGroup(group);
                    ViewCriteria lCriteria = partsView.getViewCriteria("YOUR_VC_NAME");
                    partsView.applyViewCriteria(lCriteria);
                    partsView.executeQuery();
                    ...
                    (Code not tested: done from memory :))

                    Timo
                    Hi Timo,

                    Thanks for the code...just to clarify as I would like to really understand this please..

                    1. So my idea to supply the bind variables at runtime is not possible unless I edit my view query to add and include the :Group bind variable explicitly?

                    2. From the code above, do I not have to set the :Code also in the view criteria. (I know it is just from memory so I was just verifying :) )


                    Thanks.
                    • 8. Re: Other Bind Variables is not applied
                      Timo Hahn
                      Neliel,
                      1) No, it'S possible. You define the part of the query at design time (it's possible to create the ViewCriteria (VC) at runtime too, but I guess it's not needed here). What you do is to add the query part having the code for the 'group' filter at runtime. the definition of the query part is done at design time. Essentially you define the part for the group as you did for the code, but add the group part only when the am method is called.

                      2) as the code part is part of the base query (defined in the VO query) you don't need to add it once more to the VCyou build for the group. The framework add only the VC part to the base query:
                      base query: select * from table where attr = :code
                      VC (group startswith bindvar): group = :group || '%'
                      result: select * from table where attr = :code and (group = :group || '%')

                      In the am method you still have to set the bind variable for the code (the one you get as parameter, sorroy I forgot to add this in the code) and the group bind variable.

                      Timo