12 Replies Latest reply on Jul 6, 2007 4:06 PM by Sumit Chandra Sharma

    setWhereClause - Filtering parameters that are not being selected

    579214
      Hi,

      How should I use the setWhereClause if I want to filter parameters that are not being selected?

      Ex.

      select distinct var1, var2
      from
      tab1, tab2, tab3
      where
      tab1.a=tab2.a and
      tab2.b=tab3.b

      So I want to use the setWhereClause like that:
      setWhereClause("tab2.xyz=''test");

      If I do that I receive the following error:
      java.sql.SQLException: ORA-00904: invalid column name

      Thanks,
      Marcelo
        • 1. Re: setWhereClause - Filtering parameters that are not being selected
          Sumit Chandra Sharma
          Marcelo,

          Please check the where clause in your case, the value test shuold be in single quotes.

          Thanks
          • 2. Re: setWhereClause - Filtering parameters that are not being selected
            579214
            You are right, I just mistyped it on the post. But the error still occurs on the code.

            So, that is any way to filter a select using setWhereClause that are not on the select clause?

            Thanks,
            Marcelo

            Message was edited by:
            Murad
            • 3. Re: setWhereClause - Filtering parameters that are not being selected
              557833
              I guess that you are not providing the correct column alias and hence the error. Otherwise provide the recent test case.

              --Shiv                                                                                                                                                                                                                                                                   
              • 4. Re: setWhereClause - Filtering parameters that are not being selected
                Sumit Chandra Sharma
                Marcelo,

                I think if you correct that the error should not occur. The setwhereclause(in VOImpl/AMImpl) would not mind whether you have the column selected in the select clause or not.

                Can you please confirm that after adding the column the issue did not come and if you did not have the column in the where clause it was reproducible.

                At the same time, also check that the aliases donot create ambigiousness. I mean no two aliases are the same.
                • 5. Re: setWhereClause - Filtering parameters that are not being selected
                  579214
                  That is right... I don´t have a alias because the column is not on the select statement.

                  Bellow is the query:
                  select distinct
                       a.person_id
                       ,a.last_name nome
                  from
                       per_all_people_f a
                       ,per_addresses b
                       ,per_competences f
                       ,per_competence_definitions h
                       ,per_competence_elements e
                  ,per_previous_employers c
                  ,per_previous_jobs d
                  where
                       a.business_group_id = 511
                       and a.person_id = b.person_id(+)
                       and (h.segment1 in ('EDU','IDI','TRE') or h.segment1 is null)
                       and f.competence_definition_id = h.competence_definition_id (+)
                       and e.competence_id = f.competence_id (+)
                       and a.person_id = e.person_id (+)
                  and c.previous_employer_id = d.previous_employer_id (+)
                  and a.business_group_id = c.business_group_id (+)
                  and a.person_id = c.person_id(+)


                  In the initQuery on the AM I´m trying to execute this:
                  vo.setWhereClause("upper(b.region_2) like upper('" + estadoResidencia + "')");

                  And I´m receving the folowing error:
                  Exception Details.

                  oracle.apps.fnd.framework.OAException: oracle.jbo.SQLStmtException: JBO-27122: Erro de SQL durante a preparação da instrução. Instrução: SELECT * FROM (select distinct
                       a.person_id
                       ,a.last_name nome
                  from
                       per_all_people_f a
                       ,per_addresses b
                       
                       --formacao escolar
                       ,per_competences f
                       ,per_competence_definitions h
                       ,per_competence_elements e
                       
                       --historico profissional
                  ,per_previous_employers c
                  ,per_previous_jobs d
                  where
                       a.business_group_id = 511
                       and a.person_id = b.person_id(+)
                       
                       and (h.segment1 in ('EDU','IDI','TRE') or h.segment1 is null)
                       and f.competence_definition_id = h.competence_definition_id (+)
                       and e.competence_id = f.competence_id (+)
                       and a.person_id = e.person_id (+)

                  and c.previous_employer_id = d.previous_employer_id (+)
                  and a.business_group_id = c.business_group_id (+)
                  and a.person_id = c.person_id(+)) QRSLT WHERE (upper(b.region_2) like upper('MG'))
                       at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:891)
                       at oracle.apps.fnd.framework.OAException.wrapperException(OAException.java:865)
                       at oracle.apps.fnd.framework.OAException.wrapperInvocationTargetException(OAException.java:988)
                       at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:211)
                       at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)
                       at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:749)
                       at technip.oracle.apps.per.irc.candidateSelfService.server.webui.ResumeSearchCO.processFormRequest(ResumeSearchCO.java:70)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:804)
                       at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
                       at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1156)
                       at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1000)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:966)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:821)
                       at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
                       at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1000)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:966)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:821)
                       at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
                       at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
                       at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2658)
                       at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1665)
                       at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:502)
                       at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:423)
                       at OA.jspService(OA.jsp:40)
                       at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
                       at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
                       at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
                       at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
                       at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
                       at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
                       at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
                       at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
                       at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
                       at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
                       at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
                       at java.lang.Thread.run(Thread.java:534)
                  ## Detail 0 ##
                  java.sql.SQLException: ORA-00904: nome inválido de coluna (invalid column name)

                       at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134)
                       at oracle.jdbc.ttc7.TTIoer.processError(TTIoer.java:289)
                       at oracle.jdbc.ttc7.Oall7.receive(Oall7.java:583)
                       at oracle.jdbc.ttc7.TTC7Protocol.doOall7(TTC7Protocol.java:1986)
                       at oracle.jdbc.ttc7.TTC7Protocol.parseExecuteFetch(TTC7Protocol.java:1144)
                       at oracle.jdbc.driver.OracleStatement.doExecuteQuery(OracleStatement.java:2548)
                       at oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:2933)
                       at oracle.jdbc.driver.OraclePreparedStatement.executeUpdate(OraclePreparedStatement.java:650)
                       at oracle.jdbc.driver.OraclePreparedStatement.executeQuery(OraclePreparedStatement.java:578)
                       at oracle.jbo.server.QueryCollection.buildResultSet(QueryCollection.java:627)
                       at oracle.jbo.server.QueryCollection.executeQuery(QueryCollection.java:515)
                       at oracle.jbo.server.ViewObjectImpl.executeQueryForCollection(ViewObjectImpl.java:3347)
                       at oracle.jbo.server.OAJboViewObjectImpl.executeQueryForCollection(OAJboViewObjectImpl.java:828)
                       at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQueryForCollection(OAViewObjectImpl.java:4504)
                       at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:574)
                       at oracle.jbo.server.ViewRowSetImpl.execute(ViewRowSetImpl.java:544)
                       at oracle.jbo.server.ViewRowSetImpl.executeDetailQuery(ViewRowSetImpl.java:619)
                       at oracle.jbo.server.ViewObjectImpl.executeDetailQuery(ViewObjectImpl.java:3311)
                       at oracle.jbo.server.ViewObjectImpl.executeQuery(ViewObjectImpl.java:3298)
                       at oracle.apps.fnd.framework.server.OAViewObjectImpl.executeQuery(OAViewObjectImpl.java:440)
                       at technip.oracle.apps.per.irc.candidateSelfService.server.ResumeSearchAMImpl.initQuery(ResumeSearchAMImpl.java:121)
                       at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
                       at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
                       at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
                       at java.lang.reflect.Method.invoke(Method.java:324)
                       at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:190)
                       at oracle.apps.fnd.framework.server.OAUtility.invokeMethod(OAUtility.java:153)
                       at oracle.apps.fnd.framework.server.OAApplicationModuleImpl.invokeMethod(OAApplicationModuleImpl.java:749)
                       at technip.oracle.apps.per.irc.candidateSelfService.server.webui.ResumeSearchCO.processFormRequest(ResumeSearchCO.java:70)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:804)
                       at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
                       at oracle.apps.fnd.framework.webui.OAPageLayoutHelper.processFormRequest(OAPageLayoutHelper.java:1156)
                       at oracle.apps.fnd.framework.webui.beans.layout.OAPageLayoutBean.processFormRequest(OAPageLayoutBean.java:1579)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1000)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:966)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:821)
                       at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
                       at oracle.apps.fnd.framework.webui.beans.form.OAFormBean.processFormRequest(OAFormBean.java:395)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:1000)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequestChildren(OAWebBeanHelper.java:966)
                       at oracle.apps.fnd.framework.webui.OAWebBeanHelper.processFormRequest(OAWebBeanHelper.java:821)
                       at oracle.apps.fnd.framework.webui.OAWebBeanContainerHelper.processFormRequest(OAWebBeanContainerHelper.java:363)
                       at oracle.apps.fnd.framework.webui.beans.OABodyBean.processFormRequest(OABodyBean.java:363)
                       at oracle.apps.fnd.framework.webui.OAPageBean.processFormRequest(OAPageBean.java:2658)
                       at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:1665)
                       at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:502)
                       at oracle.apps.fnd.framework.webui.OAPageBean.preparePage(OAPageBean.java:423)
                       at OA.jspService(OA.jsp:40)
                       at com.orionserver.http.OrionHttpJspPage.service(OrionHttpJspPage.java:56)
                       at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:317)
                       at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:465)
                       at oracle.jsp.runtimev2.JspServlet.service(JspServlet.java:379)
                       at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
                       at com.evermind.server.http.ServletRequestDispatcher.invoke(ServletRequestDispatcher.java:727)
                       at com.evermind.server.http.ServletRequestDispatcher.forwardInternal(ServletRequestDispatcher.java:306)
                       at com.evermind.server.http.HttpRequestHandler.processRequest(HttpRequestHandler.java:767)
                       at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:259)
                       at com.evermind.server.http.HttpRequestHandler.run(HttpRequestHandler.java:106)
                       at EDU.oswego.cs.dl.util.concurrent.PooledExecutor$Worker.run(PooledExecutor.java:803)
                       at java.lang.Thread.run(Thread.java:534)
                  • 6. Re: setWhereClause - Filtering parameters that are not being selected
                    557833
                    Use table name for "b" in statement b.region_2 i.e. per_addresses.region_2

                    --Shiv                                                                                                                                                                           
                    • 7. Re: setWhereClause - Filtering parameters that are not being selected
                      579214
                      Here is the combinations that I tried:

                      Include per_addresses.region_2 on the select and "vo.setWhereClause("upper(per_addresses.region_2) like upper('" + estadoResidencia + "')");" on the AM - Did not work

                      Include per_addresses.region_2 on the select and "vo.setWhereClause("upper(region_2) like upper('" + estadoResidencia + "')");" on the AM - Did not work

                      Include b.region_2 on the select and "vo.setWhereClause("upper(b.region_2) like upper('" + estadoResidencia + "')");" on the AM - Did not work

                      Include b.region_2 on the select and "vo.setWhereClause("upper(region_2) like upper('" + estadoResidencia + "')");" on the AM - Did work!!!

                      Remove b.region_2 from the select and "vo.setWhereClause("upper(region_2) like upper('" + estadoResidencia + "')");" on the AM - Did not work

                      Remove b.region_2 from the select and "vo.setWhereClause("upper(per_addresses.region_2) like upper('" + estadoResidencia + "')");" on the AM - Did not work
                      • 8. Re: setWhereClause - Filtering parameters that are not being selected
                        557833
                        Are you getting the same error message in all the cases ?

                        --Shiv                                                                                                                                                                                               
                        • 9. Re: setWhereClause - Filtering parameters that are not being selected
                          579214
                          No.

                          When include per_addresses.region_2 on the select - "The query is not valid."

                          When setWhereClause is:
                          b.region_2
                          per_addresses.region_2
                          Then "Invalid column name"

                          When include b.region_2 on the select and setWhereClause equals region_2 - It works.

                          Does that means that to use setWhereClause the column has to be on the select?

                          Thanks,
                          Marcelo

                          Message was edited by:
                          Murad
                          • 10. Re: setWhereClause - Filtering parameters that are not being selected
                            555256
                            Marcelo,Sumit and Murad,
                            As far as I know, the setwhere clause is executed on the run on VO query hence ,i.e., the qrslt clause which binds the th VO query for bind variables actually stands for query result, so the bind variables must be included in query result, hence, its absolutely necessary that your VO query select statement should include the columns in its select clause, which you later can filter in select where clause. I hope i am clear.
                            --Mukul                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
                            • 11. Re: setWhereClause - Filtering parameters that are not being selected
                              579214
                              Thank you guys for the help.

                              I think I will use the getQuery and setQuery.

                              Marcelo
                              • 12. Re: setWhereClause - Filtering parameters that are not being selected
                                Sumit Chandra Sharma
                                Yes Mukul, I too observed that after I had a relook at some of the old code.