5 Replies Latest reply: Jul 5, 2013 11:28 AM by Patrick Harriss RSS

    Is it possible to do a sub select in a view criteria

    Patrick Harriss
      Using JDev 11.1.1.6 I am wondering if it is possible to do a sub select in my view criteria for a given view object.

      Say for example I have two tables that are parent child one for lookups and one for lookup values. The lookup_type is stored in the lookups table and there is a 1:M relationship between the lookups and lookup values which are joined together by an ID. In the view criteria on the lookup values view object I want to set a view criteria that will retrieve the lookup values for a give type so my criteria needs to look like this.

      lookupvalues.lookups_row_id = (SELECT lookups_row_id FROM lookups WHERE lookups.lookup_type = 'STATUS')

      Doing the above doesn't work. I am able to hard code a value like below but that doesn't do me much good. I've taken the query that it produces and run it in sql plus and it runs and returns fine but doesn't work when run inside the ADF application. Any idea if we can use a sub query in view criteria?

      lookupvalues.lookups_row_id = 123

      FYI - I was able to find one solution to his which is to do a join on this lookup values view object with the lookup view object and then added the lookup_type attribute to the lookup values view object so I can add that attribute directly to the view criteria but I would really rather just do a sub query if I can.

      Thanks.
        • 1. Re: Is it possible to do a sub select in a view criteria
          Jang-Vijay Singh
          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:
          http://weblog.singhpora.com/2010/01/line-item-search.html
          • 2. Re: Is it possible to do a sub select in a view criteria
            Patrick Harriss
            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
            lookup_value
            lookup_meaning

            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?
            • 3. Re: Is it possible to do a sub select in a view criteria
              Jang-Vijay Singh
              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..
              • 4. Re: Is it possible to do a sub select in a view criteria
                Patrick Harriss
                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?
                • 5. Re: Is it possible to do a sub select in a view criteria
                  Patrick Harriss

                  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

                  <ViewCriteriaItem

                          Name="Salary"

                          ViewAttribute="Salary"

                          Operator=">"

                          Conjunction="AND"

                          Required="Optional"

                          IsSqlFragment="true"

                          Value="(SELECT AVG(SALARY) AVG_SAL FROM EMPLOYEES)">

                  </ViewCriteriaItem>