Is your view criteria defined on the master view object?
If the master to detail relationship is 1 to many (like Department to Employees), is your main view criteria defined on the master but you also searching based on some attribute of the detail?
It would be helpful if you could explain it using an emp-dept example, and see if this helps:
Jang-Vijay Singh, the view criteria is on the detail view object. Let me see if I can define the situation better.
Lookups Table - Master
lookups_id - PK
lookups_type - String
Lookup Values Table - Detail
lookup_values_id - PK
lookups_id - FK to Lookups Table
For a given Lookup you can have 1 to many lookup values. I am trying to use view criteria on the lookup values table to build a dropdown list based on a lookups_type. So in the lookup_values (detail) view criteria I created a view criteria with the following
lookup_values.lookups_id = (SELECT lookups_id FROM lookups WHERE lookups.lookups_type = 'STATUS')
The query validates and if I run it in SQL plus it returns exactly what I want but when I run it through ADF it doesn't. This makes me wonder if sub queries are supported in view criteria in ADF?
lookup_values.lookups_id = (SELECT looktups_id FROM lookups WHERE lookups.lookups_type = 'STATUS')You can do it using inline queries and bind variables etc.
But I think you'd be better off having both lookups and lookup_values joined in your LookupValuesVO view object.
This combined view object would have the same number of rows as the detail (lookup_type)
Then, you can filter out the combined detail rows by lookup_type.
That's an effective ADFbc model for this kind of a requirement..
Jang-Vijay Singh as I mentioned in my initial post I was able to get it working by joining the two tables at the view object level and then adding the needed attributes from the master table to the detail table so yes that will work. I was just more curious as to why the sub query wasn't working or if it is even supported?
I realize this is an old thread but I stumbled across a solution to this. It looks like in order to use an SQL statement in the value field of your view criteria you need to go in to the view object source and set IsSqlFragment="true" for that particular view criteria item. For instance if you wanted to use a view criteria where the salary was greater than the average salary then your view criteria item would look like
Value="(SELECT AVG(SALARY) AVG_SAL FROM EMPLOYEES)">