ADF Query with Parameters and List of Values - Part II

In the previous blog entry I showed how to do a search page using the execute with parameters operation. This entry is a little more up-to-date and uses the new view criteria and query components that have been added in JDeveloper 11g.
One nice aspect here is that it also lets you change the UI component for the parameters to be input with list of values.

Here is the demo:


Great Jdeveloper Demos!! i use execute with parameters form and populate a form and/or table. i would like to have another button (show details) that passes same params to a query and displays a table with details on same page? do you know of any online examples?

Posted by Mike on January 08, 2010 at 06:20 AM PST #

Mike, Glad you liked the demo. For the scenario you are describing it seem that all you need is a service method at the AM level that you expose to the client. Then you can drag this method and drop it as an ADF parameter form. The method can update a VO definition (set the Where for example) and then you can just add that VO to your page. Shay

Posted by shay on January 08, 2010 at 08:22 AM PST #

This is nice demo, I liked it. The problem scenario I am working on is bit different. I have a VO query like below: select job_id, sum(salary) as TotalSal from employees where dept=:p_dept group by job_id I want to show the combo box for department id in UI. User will select the department id and he will be able to see the pie chart for the above query. Can you please help me with this? -Suresh

Posted by Suresh on May 20, 2010 at 07:06 PM PDT #

Suresh, seems like an ideal situation for using a navigation list, see this past entry: basically define the department VO as a master for a VO that has your sum query. Drop the first VO as a navigation list - and the detail VO as a graph and you are done.

Posted by shay.shmeltzer on May 21, 2010 at 07:43 AM PDT #

Hello Shay I am having a different use case and I don't know exactly how can I implement it. I have two VO's with different queries: No1 select * from table where param1, param2 No2 select count(*) from table where param1, param2 (The selects are generic) I can implement it into a single select: select *, (select count(*) from table where param1, param2) where param1 param 2 but it will be very costly in resources, and this is why I decided to create 2 VOs, but my problem is that I must make them react to the same set of params. Can you help me with a solution? Thanks, Angel

Posted by Angel on March 31, 2011 at 08:52 PM PDT #

Angel, See if this blog entry helps:

Posted by shay.shmeltzer on April 01, 2011 at 01:43 AM PDT #

Hi Shay

Nice demo! What I am looking for is a way to display the department name on the query component after it has been selected by the list of values. Just a nice confirmation to the user that they have selected the correct one they wanted. In my example there are too many items in the lookup to use the choice list. Any idea if this can be done?

thanks and regards

Posted by guest on June 28, 2011 at 07:19 PM PDT #

Lisa - how about using an LOV component in your af:query instead?
Similar to this:

Posted by Shay on July 01, 2011 at 04:08 AM PDT #

Hi Shay

Thanks for the reply.

I am already using an LOV component in the query component. After the user has made the selection from the LOV, I would like the descriptive name of the selection, rather than the meaningless code, to display on the query component. The analogy in your demo, would be that after choosing Seattle from the city LOV, then on the query component we would see the meaningful Seattle rather than the meaningless 1700.


Posted by guest on July 03, 2011 at 07:08 PM PDT #

Lisa, you can add the translation field to your VO that gets the deparment name. Basically create the VO so it based on the EMP as an updateable EO and the Dept EO as a refence EO - and include the department_name field in the VO.

Posted by Shay on July 05, 2011 at 09:04 AM PDT #

Shay, this is almost what I want, thanks. I would just like to have the department id on the employee query component as well as the department name, and would like to have both the department id and name populated from the lov. I thought it might work to add the other item under Edit List of Values, Configuration tab, List Return Values (where it says you can "map any supplemental values that your list returns to the base view object") but this doesn't seem to work in a query component.

Posted by Lisa on July 05, 2011 at 09:44 PM PDT #

I am looking to accomplish the very same thing that Lisa is asking. I would like to populate both department id and department name from one LOV. I would be very interested to know if this is possible inside the query component.

Posted by Rob on July 14, 2011 at 05:29 AM PDT #

Rob and Lisa, it seems that this is not possible right now in the built-in query component. I filed a Bug/ER for this - in the meantime if you have to have this functionality you'll need to build your own query page not using the af:query component.

Posted by Shay on July 14, 2011 at 06:35 AM PDT #

I want to attach method parameters with LOV other than choice one list. And i want to cascade with each other. Could you please guide me

Posted by BIJESH k on October 10, 2011 at 05:15 PM PDT #

Bijesh - see if this helps you:

Posted by Shay on October 12, 2011 at 04:39 AM PDT #

Hi Shay,
I'm facing a little different scenario of your sample.

MODEL scenario.
I have a main ViewObject with Search Criteria and LOVs for 3 fields, and all of them receives the same parameter, the logged user role.

Checking the logged user role, I must to filter what rows will be displayed. So, the query for the main VO is like this:
SELECT * FROM myTable WHERE userRole = :loggedUserRole

The queries for my 3 LOVs are similar, the only change is the field.
SELECT DISTINCT field_1 FROM myTable WHERE someFilterField IN (
SELECT otherField FROM myTable WHERE userRole = :loggedUserRole

SELECT DISTINCT field_2 FROM myTable WHERE someFilterField IN (
SELECT otherField FROM myTable WHERE userRole = :loggedUserRole

SELECT DISTINCT field_3 FROM myTable WHERE someFilterField IN (
SELECT otherField FROM myTable WHERE userRole = :loggedUserRole

field_1 <> field_2 <> field_3 <> userRole. All different fields for the same table.

I have a home page in adfc-config unbounded task flow.
I have an unbounded task flow (usersTaskFlow), in where I have a route and two fragments.
In the home page, I dropped the usersTaskFlow. With this, I can control what fragment will be shown depending on the user Role.

In the fragment, I have an af:query and an af:table to show the results, the af:query depends on a Search Criteria defined in my VO.
So, the user can search for the 3 fields with LOVs, configured like Input Text with List Of Values.

So, I need to know, how can I assign the same parameter for all my View Objects automatically.
I mean, my main filter here is the logged user role, this data is not selectable in the app. So, this must be done transparently for the user.

I will appreciate so much your comments.

Posted by JBaste on May 14, 2012 at 08:00 AM PDT #

Post a Comment:
  • HTML Syntax: NOT allowed

I'm a Director of Product Management for Oracle's Cloud Development Tools and Frameworks.
Follow me:

« July 2016