This discussion is archived
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 Newbie
Currently Being Moderated
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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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?