2 Replies Latest reply: Nov 20, 2012 5:58 AM by 971908 RSS

    Bind variables in a view object: WHERE x IN (option1,option2,option3)

    971908
      Hi,

      in my application module, I use a view object with this query:

      SELECT po_header_id PoHeaderId,LISTAGG(po_line_id,',') WITHIN GROUP (ORDER BY po_header_id) PoLineIds FROM xxpo_dropship_documents_v WHERE po_line_id IN ( :1 ) GROUP BY po_header_id

      The bind variable should be substituted by a list of integers, such as 15523595,15537717, 15537715.

      These integers are listed in a string variable called poLineIdsIn.

      Is there any way to accomplish this?

      These are the lines of code I used so far:

      ViewObject vo = getXxpoDropshipDocumentsGroupVO1();
      vo.setWhereClauseParam(0, poLineIdsIn);vo.setWhereClauseParam(0, poLineIdsIn);

      As poLineIdsIn is a string, it will throw an sql error this way.



      Alternatively, I tried this:
      ViewObject vo = am.createViewObjectFromQueryStmt(
      "XxpoDropshipDocumentsGroupVO1",
      "SELECT po_header_id PoHeaderId,LISTAGG(po_line_id,',') WITHIN GROUP (ORDER BY po_header_id) PoLineIds" +
      " FROM xxpo_dropship_documents_v " +
      " WHERE po_line_id IN (" + poLineIdsIn + ")" +
      " GROUP BY po_header_id");

      Unfortunately, xxpo_dropship_documents_v is a view that needs some values from the user environment. This doesn't seem to work for createViewObjectFromQueryStmt.


      Any idea how I can solve this issue?

      Thanks for any advice.
        • 1. Re: Bind variables in a view object: WHERE x IN (option1,option2,option3)
          Sushant Sharma
          Hi,

          You can write simple select statement in VO query without any where clause like this:
          SELECT po_header_id PoHeaderId,LISTAGG(po_line_id,',') WITHIN GROUP (ORDER BY po_header_id) PoLineIds FROM xxpo_dropship_documents_v

          and then set where clause using vo.setWhereClause("<condition>");
          after this, use vo.setWhereClauseParam() to set the parameters values.

          --Sushant                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
          • 2. Re: Bind variables in a view object: WHERE x IN (option1,option2,option3)
            971908
            Hi,

            thanks for your suggestion.

            However, then I can't use the GROUP BY anymore, can I?

            And I noticed that it still doesn't use the user environment to the database. How can I query environment dependent views, in general?