Monday, July 1, 2013

Advanced View Criteria Implementation in ADF BC

I would like to walk through advanced options in ADF BC View Criteria. It goes easy, when defining pretty much standard criteria operators and comparing attribute from VO with literal on bind variable. But when it comes to implement more complex use case with SQL expression or non standard operator, its when developers usually drop it and implement static WHERE clause. Nothing bad to use static WHERE clause and in some situations there is no other choice as only to use static WHERE clause - I will describe in my next post. Today post is about advanced declarative View Criteria features only.

Here is my sample application as usual - CustomCriteriaOperationApp.zip. View Criteria is implemented for Employees VO. Besides regular attributes, there is one pointing to SQL fragment (Salary) and there is custom IN operator implementation:


As you can see Salary attribute implement > operator and it contains SQL statement for the literal. I set SQLFragment = true property in VO XML source code, this means SQL statement will be translated to SQL on runtime. This is convenient when you want to implement standard operator, but value is retrieved from custom SQL statement returning employees with salary above average.

If you want to implement operator to be custom as well - no problem, this is possible. As this example below implements IN operator and applies it for SQL statement highlighted (editable in VO XML source code only):


Here you can see in the source code, how custom operator is defined along with SQL statement to retrieve value:


Custom operator SQL statement is using bind variable for region ID. This bind variable is defined as required and initialized through View Criteria transient attribute. I need this in order to be able to render on UI search field for region ID:


Transient region ID attribute does nothing, is used only to be rendered as search parameter in the UI. Bind variable is mapped to this transient attribute and gets initialized as soon as user types and submits search action. This is how bind variable value is set and later used in custom operator SQL statement. Transient attribute defined in the VO:


ADF UI renders search block automatically, it displays these attributes marked as visible - First Name, Last Name and Region ID:


Result set is filtered by criteria from SQL fragment - employees with salary value above average and from departments in Europe, as selected in the choice list.

13 comments:

Anonymous said...

hello! Nice!

ILya Cyclone said...

Wow, great! I was waiting for such func for eternity!

Shanto said...

Thank you.. That was much needed.

Andrej Baranovskij said...

My pleasure ;)

Ramesh said...

we have a requirement to create UI similar to jdeveloper view crtieria editor. Users want to create theri own complex criteria and save it. The query component supports either OR or AND does not support complex grouped criteria.

ex (DEPtNO = 10 AND SALARY = 1000) OR (DEPtNO = 20 AND SALARY = 500)

Any suggestion on what would be the best way to implement?

My first thought is to have DB tables that mimic ViewCriteria xml.

Andrej Baranovskij said...

Yes, check this post - http://andrejusb.blogspot.com/2013/07/joining-static-where-clause-and-view.html

Andrejus

Unknown said...

Hi Andrej

can u clarify my below mentioned doubt

the query is
select * from departments where department_id in(select department_id from employees where first_name='smith')

it says that
searching on a first name of an employee should display department table information relevant to that employee

how to implement this query as search criteria.
I have a doubt that can we implement sub query in view criteria

Vinay M said...

Hi Andrejus,

As in your example you are able to select IN from operator, how did you that I see no option available when I create ViewCriteria.
Have tried both in 11g and 12c version of Jdeveloper.
Let me know how to achieve this.
Thanks in advance.

Andrej Baranovskij said...

Hi,

This is custom operator, check 3rd screenshot.

Andrejus

Trinh Dzung said...

Hi Andrejus,
Can you tell me? How do I programming get list of Value for SelectManyChoice in af query?
all the case i get value succed, except selectManyChoics just get index' value include special charater as '@'.
Thank your advice.

Anonymous said...

Hi, Is there a way to pass an array to a bind variable of a view object that is in IN CLAUSE of the sql query??

Thanks,
Sid

Habib said...

that's great,
I really appreciate it

Anonymous said...

Hi Andrew,

Thanks for your post.

As per our requirement,

We meed to choose month from choice list and it need to be mapped with date column.
2 attributes in VO
Month - Transient string
To_Month - Date column
2 attributes in View criteria
Month=:month (Which has values like Jan,Feb,..)
To_Month= TO_DATE(CONCAT('01-',:ToMonth),'DD-MM-YY')

So i need know can i pass bind variable in it? or do i have any other option?