Appending programmatic Where Clause to VO Query instead of QRSLT

It is usual in ADF to use vo.setWhereClause() programmatically and refresh the table data in the UI.

But, if you have observed the query which gets executed, it would be like below.

SELECT * FROM (SELECT Employees.EMPLOYEE_ID,
       Employees.FIRST_NAME,
       Employees.LAST_NAME,
       Employees.EMAIL,
       Employees.PHONE_NUMBER,
       Employees.HIRE_DATE,
       Employees.JOB_ID,
       Employees.SALARY,
       Employees.COMMISSION_PCT,
       Employees.MANAGER_ID,
       Employees.DEPARTMENT_ID
FROM EMPLOYEES Employees) QRSLT  WHERE (DEPARTMENT_ID = 10)

The where clause is set by using the below code (it is straight forward)

You can clearly see the Where Clause is appended to the QRSLT and not to the actual query.

If you see it in a larger scope, it causes performance issues with tables having large data or on complex queries because inner query gets executed first and then filter is applied.

And one more point to note is, the column you add in the where clause SHOULD BE there in the select clause.

So, how to overcome this? How to append the Where clause to the query itself?

Solution:

You need to generate the VOImpl class for the ViewObject and override the create() method and put setNestedSelectForFullSql(false); as below

After this, you again run the page and test it. Now, the Where Clause gets appended to the VO query itself instead of the QRSLT just like below

SELECT Employees.EMPLOYEE_ID,

       Employees.FIRST_NAME,

       Employees.LAST_NAME,

       Employees.EMAIL,

       Employees.PHONE_NUMBER,

       Employees.HIRE_DATE,

       Employees.JOB_ID,

       Employees.SALARY,

       Employees.COMMISSION_PCT,

       Employees.MANAGER_ID,

       Employees.DEPARTMENT_ID

FROM EMPLOYEES Employees WHERE (DEPARTMENT_ID = 10).

Hope it is clear and useful :)