This discussion is archived
7 Replies Latest reply: Nov 15, 2012 10:39 PM by 973049 RSS

Dynamic where clause

973049 Newbie
Currently Being Moderated
Hi,

In a standard search page, there are say 4 fields. 1 Go and 1 clear button.

When we hit Go, the query that runs is :
Select xyz,xyz_1,xyz_2
from Table
where po_number = :1

If we have values in field2, then the where clause gets appended by
AND po_line = :2 and so on.

Now I add another field through personalization and extend the CO to append the VO query:
vo.setWhereClause("xyz = :1");
vo.setWhereClauseParams(null); // Always reset
vo.setWhereClauseParam(0, Country);

But it gives an error, wrong index, as the index 1 is already used._Here_ is the issue.

How will I know which index number it will get. If I select 1 param, the index will be :1 if I select 1 more it will be :2.

So when I add a new field and pass it to view where clause, what is the best technique_ to handle the index, so that it automatically takes the next incremented value.

Explanation with an example will be highly appreciated.
  • 1. Re: Dynamic where clause
    Gyan Darpan Guru
    Currently Being Moderated
    Hi,

    In the case of dynamic where clause, we always start the indexing from the next number.

    Suppose if your View Object contain :1, then on the dynamic where clause you will always append next value, which will be :2 and so on.

    Regards,
    Gyan
  • 2. Re: Dynamic where clause
    973049 Newbie
    Currently Being Moderated
    thanks gyan..

    Yes, we do write that when we are sure about the numbers..but in my case, some times user can put 1 value, so my index will be 2, but the moment user selects a second field, my index will fail.

    I have to check wht fields user selects, take d count, and based on that i gotta assign index, that can be 1 way, but wanted to cnfrm, if any other generic method is there to do this..
  • 3. Re: Dynamic where clause
    shreevat Pro
    Currently Being Moderated
    If you don't wish to use positional parameter, I think you can go with Named Parameters and then use setNamedWhereClauseParam in VO. This way you need not worry about existing position parameter index. I haven't used this, so not sure of any impacts. Refer to the JDev guide to understand the named parameters and possible issues.

    Thanks
    Shree
  • 4. Re: Dynamic where clause
    973049 Newbie
    Currently Being Moderated
    Thanks Shreevat.

    I din't find anything like 'setNamedWhereClauseParam' in jdev guide. In some of the forums it is said, setNamedWhereClauseParam is applicable to adf and not oaf. Am not sure how much that is authentic.

    If anyone has any working code in oaf, it will be very much helpful.
  • 5. Re: Dynamic where clause
    973049 Newbie
    Currently Being Moderated
    Any update??

    I am stuck with it as with different search options, it is taking different value.
  • 6. Re: Dynamic where clause
    SumitSharma Expert
    Currently Being Moderated
    Use the following code snippet to add where clause

    setWhereClause(null);
    String s = "xyz="+vXYZ;
    setWhereClause(s);

    Using a variable (vXYZ: value that you want as filter) in string definition would save you to have a named bind parameter and still be able to add the additional bind parameter. This should work.

    Regards
    Sumit
  • 7. Re: Dynamic where clause
    973049 Newbie
    Currently Being Moderated
    Thanks Sumit.

    It works.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points