Queries that include correlated sub-queries

It is not always possible to obtain a desired result in a single query using a Report Definition. Instead, the desired level of information can be obtained from a follow-on or drill-down query based on the initial query.

XYZ Corp orders office supplies through multiple suppliers. XYZ Corp wants to see detail about the most expensive line items purchased through each supplier. XYZ has Order cases that have LineItem subcases. LineItem detail is stored in a separate table.

How would you obtain this information?

Define a subreport that obtains supplier ID and max(Price) from the line item table. Within the main report query the line item table joining to the subreport by supplier ID.

For same supplier, multiple products from the same supplier may have the same price which also happens to be the maximum price for that supplier within the order.

The result does not show line item detail.

Drill down to obtain information about the line item for the supplier that share the same price. The drill-down report could ask for distinct rows.

The syntax below achieves the result in a single query.

Select
ORD.OrderID,
LI.SupplierID,
PROD.SKU,
PROD.Detail,
LI.Price
From
Order ORD,
LineItem LI,
Product PROD
Where
LI.OrderID = ORD.OrderID
and PROD.SKU = LI.SKU
and LI Price = (select max(price)
From LineItem LI2
Where
LI2.OrderID = LI.OrderID
and LI2.Supplier_ID = LI.SupplierID)
TEMP

This type of query is called is called a correlated subquery. Note how the subquery references LineItem columns using two different aliases, LI and LI2.

If not handled properly by the database, a correlated subquery can be inefficient since it implies that a separate query will be executed for every row. Modern databases, based on what their query optimizer tells them, can “unnest” the subquery such that it acts the same as referencing a (materialized) VIEW.

Report Definitions allow a parameter to be passed to the subquery from the main report. The parameter’s value does not change from row-to-row unlike when a JOIN is defined to a particular column.

When configuring a subreport, no alias is required to reference the main report. Instead, on the right-hand side of the subreport join, simply enter a property that belongs to the main report’s class. This is where the correlation occurs.

The query syntax below was generated by the CorrelatedSubqueryTest Report Definition within the provided BookingReports ruleset. The starting point for this Report Definition was the CloneJOINTest Report Definition, also contained within the provided BookingReports ruleset. It was not necessary to have defined the CLONE join to demonstrate that a correlated subquery can be defined. What this example demonstrates is that it is also possible to reference a value on the right-hand side of the subreport correlated join that is derived from a JOIN performed by the main report.

SELECT
"PC0"."pyid" AS "pyID",
"PC0"."pystatuswork"
AS "pyStatusWork", TO_CHAR(DATE_TRUNC('day' "PC0"."pxcreatedatetime"::TIMESTAMP),
'YYYYMMDD')
AS "pyDateValue(1)",
"CLONE"."pylabel" AS "pyLabel",
"HOTEL"."srcol4" AS "pyLabel",
"PC0"."pzinskey" AS "pzInsKey"
FROM
pegadata.pc_FSG_Booking_Work "PC0"
INNER JOIN
pegadata.pc_FSG_Booking_Work
"CLONE" ON ( ( "CLONE"."pyid" = "PC0"."pyid" )
AND "CLONE"."pxobjclass" = ?
AND "PC0"."pxobjclass"
IN (? , ? ))
INNER JOIN (
SELECT
"HOTEL"."pyguid" AS "srcol1",
"HOTEL"."brand" AS "srcol2",
"HOTEL"."name" AS "srcol3",
"HOTEL"."pylabel" AS "srcol4",
"HOTEL"."pzinskey" AS "pzinskey"
FROM
pegadata.pr_FSG_Data_Hotel_e0214 "HOTEL"
WHERE "HOTEL"."pxobjclass" = ? ) "HOTEL" ON ( ( "HOTEL"."srcol4" = "CLONE"."pylabel") AND "CLONE"."pxobjclass" = ? AND "PC0"."pxobjclass" IN (? , ? ))

Note how the first example query in this section included a comparison to an aggregated value. i.e., LI Price = (select max(price) from LineItem LI2. This is something that a subreport can do that a regular JOIN cannot do. Below is another example.

Suppose you want to enforce that only one LineItem subcase within a Purchase Order can be in someone’s worklist at any given time. Parent case locking could be used to prevent two persons from working on the Purchase Order at the same time. This does not prevent simultaneous ownership of LineItem worklist assignments for the same Purchase Order.

SELECT
LI1.LineItemID from LineItem LI1, (SELECT count(*) as LI2Count
FROM
LineItem L2,
Assign-Worklist WL
WHERE
L2.PurchaseOrderID = LI1.PurchaseOrderID AND L2.pzInsKey = WL.pxRefObjectKey) A
WHERE
L1.pyStatusWork not like 'Resolved%' AND A.LI2Count = 0

The requirement could be changed, for example, to less than or equal to two (<= 2). In that case the outer query should be joined to Assign-WorkBasket to prevent a LineItem case being returned that is in someone’s worklist. Instead every LineItem case returned by the query is associated to a WorkBasket assignment.