Working with nested subqueries

A nested subquery simplifies a complex query by mapping a set of columns to a pseudo-table. You define the pseudo-table as a query that maps to an alias name in the FROM clause of an SQL query. The parent table in the SQL query joins to the pseudo-table in the same way as it joins to any other secondary table. A typical use of a nested subquery is when you need to access an aggregate value from a secondary table. This advanced feature is not normally needed in production code. Use this feature only if necessary due to the performance implications. For more information about nested subqueries, check the documentation for your database. The Oracle term for a nested subquery is an inline view. The SQL Server term for a nested subquery is a derived table. If you have questions about the usage of nested subqueries, contact Guidewire Customer Support.

For example, the following SQL query uses a nested subquery to compare the minimum value of a secondary table column with the value of a primary table column:

SELECT * 
  FROM pc_Address
  INNER JOIN ( SELECT Country, MIN(City) MinCity
            FROM pc_Address
            GROUP BY Country ) MinCityAddress
    ON pc_Address.City = MinCityAddress.City;

To create a nested subquery, call the inlineView method on the query. This method returns a query using the new nested subquery and includes all referenced columns from that query in the select statement. The inlineView method takes the following arguments:

  • joinColumnOnThisTable – The name of the join column (String).
  • inlineViewQuery – The query (Query). This argument cannot be the result of any Query method that returns a Table.
  • joinColumnOnViewTable – The name of the join column on the nested query table (String).

The method returns a new query that contains the nested subquery. The return type of the method is a Table object.

Predicates on the secondary table can use columns on the secondary table or the primary table.

For example, suppose you create two queries, an inner query and an outer query:

var innerQuery = Query.make(Address)
var outerQuery = Query.make(Address).withLogSQL(true)

Next, create a nested subquery from the inner query to add the Address#Country column to the columns that the outer query returns:

var nestedQuery = outerQuery.inlineView("Country", innerQuery, "Country")

Next, use the City column in a new predicate on the outer query:

outerQuery.compare(Address#City, Equals, 
          nestedQuery.getColumnRef(DBFunction.Min(Paths.make(Address#City))))

Test the code:

for (row in outerQuery.select()){
  print(row.DisplayName)
}

This code prints the display name of any Address entity that has a city that matches the city that is alphabetically first for a particular country. This produces the following SQL statement:

SELECT /* KeyTable:pc_address; */ gRoot.ID col0, gRoot.Subtype col1 
  FROM pc_address gRoot 
  INNER JOIN (
    SELECT address_0.Country col0, MIN(address_0.City) col1 
      FROM pc_address address_0 WHERE address_0.Retired = 0 
      GROUP BY address_0.Country) address_0 
    ON gRoot.Country = address_0.col0 
  WHERE gRoot.City = address_0.col1 AND gRoot.Retired = 0 []