When a Report Definition needs columns from two classes, for example customers and their loans, you join the classes. The join type decides which rows survive when one side has no match on the other. Pega offers three: inner, left outer and right outer.
Where to set it
Open the Report Definition and go to the Data Access tab. In the Class joins section, add the joined class and the join condition, then choose one of the row options below.
Option names and what they mean
| Option in Pega | SQL join | Rows returned |
|---|---|---|
| Only include matching rows | INNER JOIN | Only rows that have a match in both classes |
| Include all rows in this class | LEFT OUTER JOIN | Every row of the report's own class, with blanks where the joined class has no match |
| Include all rows in joined class | RIGHT OUTER JOIN | Every row of the joined class, with blanks where the report's class has no match |
A worked example
Customers (the report class) and Loans (the joined class), joined on CustomerID:
| Customer | Loan |
|---|---|
| Asha | L-101 |
| Ben | (none) |
| (none) | L-999 (orphan) |
- Inner: only Asha and L-101.
- Left outer (all rows in this class): Asha with L-101, and Ben with an empty loan column. Useful for "customers who have no loan yet".
- Right outer (all rows in joined class): Asha with L-101, and L-999 with an empty customer. Useful for finding loans with missing customer records.
Common mistakes
- Using an inner join and wondering why some customers are missing. They have no match, so they were dropped.
- A one-to-many join repeating the parent row for every child. Tick Remove duplicate rows or group the results. See removing duplicates in a Report Definition.
- Joining on a property that is not indexed, which makes the report slow on large tables.
Interview tip
Map the three Pega options to inner, left outer and right outer, then explain them with two small tables like the one above. More in the Report Definition label.
No comments:
Post a Comment