Saturday, December 15, 2012

ADF View Object - View Criteria Row with SQL Literal

View Criteria provides an ability to decoratively add a where clause fragment to view object. 

Jdeveloper GUI by default provides option to declare a view criteria items value either as a 
  1. Literal 
  2. Bind Variable. 
There is also support for adding a literal that would be treated as a 
  1. SQL fragment
Below steps show how to configure
      1.       Create View Criteria, Add a view criteria item, Select operator as HireDate, Operator as Between and Operand as Literal

       2.       Go to the Source Tab of the View Object and add an IsSqlFragment attribute with value true in view criteria item value as below.



          3.       From the View Criteria Editor provide values sysdate-3650 and sysdate


       4.       Click on Test button to validate the Query


       5.       To Test the Apply the View Criteria in Application Module Data Model.


        6.       Run The Query from SQL WorkSheet and AM tester to compare the results.


       7.       The Sample application attached here. Let me know if you have any questions.
       8.       You can also use the same logic for other operators; ViewCriteriaRow also supports the IsSqlFragment attribute. Here is an example.

2 comments:

  1. This option works fine in an App. Module. But, when the view criteria is added to a Search using Query Panel with Table in a JSF page, it throws error "Caused by: java.lang.IllegalArgumentException: Cannot convert sysdate-3650 of type class java.lang.String to class java.sql.Date" at run time.

    Any additional code to be added for a JSF page?

    ReplyDelete
  2. This helped me out! Thanks so much! 11 years later! Lol.

    ReplyDelete

Related Posts Plugin for WordPress, Blogger...