This discussion is archived
3 Replies Latest reply: Feb 17, 2013 8:32 AM by Timo Hahn RSS

Unexpected table filtering results

LoriTillery Newbie
Currently Being Moderated
Jdeveloper 11.1.2.3.0

Table component on a viewobject with filters created using this link: http://www.oracle.com/technetwork/developer-tools/adf/learnmore/16-custom-table-filter-169145.pdf

Table shows list of events including event name. If I filter on an event name that has the word 'and' in it, the query will break apart the name into two separate where clause params. I can see it happening if I turn on sql debugging:
<ADFLogger> <begin> Executing iterator binding
<ViewRowSetImpl> <doSetWhereClauseParam> [6610] GSPEventMgmtView1 ViewRowSetImpl.doSetWhereClause(-1, vc_temp_1, Connected Cloud%)
<ViewRowSetImpl> <doSetWhereClauseParam> [6611] GSPEventMgmtView1 ViewRowSetImpl.doSetWhereClause(-1, vc_temp_2, Evolved TP Bootcamp%)

The actual event name is "Connected Cloud and Evolved TP Bootcamp"

I understand that 'and' is a reserved sql keyword; however, since it is embedded in a string, I did not expect the filter query mechanism to parse it this way and break it apart into two separate pieces for the where clause.

Is there just some parameter that I missed somewhere? I searched through the forum and also looked at available parameters on my view object and page binding iterator but I don't see anything obvious.

Please let me know if additional code is needed to help troubleshoot.

Thanks, Lori
  • 1. Re: Unexpected table filtering results
    Timo Hahn Oracle ACE
    Currently Being Moderated
    The problem is, as you already found out, that 'and' is a reserved word for 'Query by example' (QBE) and is handled by the sql builder. There are other character (e.g. '<' and '>') or words ('and and 'or') which are handled as operators and not as string value ot search.
    Right now there is no easy configuration or property to influence this. What you can do is to override the method
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem vci)
    which returns the sql used to query an attribute. A sample may look like
    @Override
    public String getCriteriaItemClause(ViewCriteriaItem vci)
    {
      if (vci != null)
      {
        AttributeDef attrDef = vci.getAttributeDef();
        String attrName = attrDef.getName();
        if ("java.lang.String".equals(attrDef.getJavaType().getName()) && vci.getValue() != null)
        {
          String colName = vci.getViewCriteria().isCriteriaForQuery() ? vci.getColumnNameForQuery() : attrName;
          return String.format("(%s = 'xxx%sxxx'))", colName, vci.getValue());
        }
      }
      return super.getCriteriaItemClause(vci);
    }
    which adds 'xxx' around each string parameter. I'm not sure if the method is called once or twice if a 'and' appears in the string. If it's called twice you need to use another word instead of 'and' and check the parameter if the new and (e.g. 'xandx') is in the parameter and replace it with 'and'.

    Timo
  • 2. Re: Unexpected table filtering results
    LoriTillery Newbie
    Currently Being Moderated
    Thank you Timo. I will implement your solution and post back results.
  • 3. Re: Unexpected table filtering results
    Timo Hahn Oracle ACE
    Currently Being Moderated
    Blogged solution here http://tompeez.wordpress.com/2013/02/16/jdeveloper-11-1-1-6-0-escape-qbe-operators-in-filterable-tables/

    Timo

Legend

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