Applies to: Insight 5.X
Date: September 14, 2016
Generally speaking, it is possible to use this approach for selecting a specific single Child row in a typical one-to-many LEFT JOIN.
Some relevant points related to this approach are:
This approach is considered much more “expensive”, as it will generally take longer to execute and will consume much more RAM than the preferred approach:
The preferred approach utilizes several Records, the first of which uses an aggregation like MIN or MAX on a Child field to get the desired Child row during the first Record JOIN. Additional Child fields are then obtained through subsequent JOINs in other Records which use the original JOIN condition(s), as well as the aggregated field as an extra JOIN condition, to JOIN back to the Child table a second/third time etc.
Data Reduction Options - Using Table Fields on the JOIN Advanced Options Screen
The “key” Table Fields chosen on the Data Reduction Options pop-up act as the “composite grouping” by which each First or Last Child row is selected (based on the selected option). Typically this is just the same field or fields that were used for the JOIN condition itself.
The First or Last Child row encountered can be controlled by the Orders pop-up screen launched from the Record’s SQL tab:
Note: If you select additional key Table Fields from the Parent table which are not mapped to actual Record fields, then you will have an item count mis-match that prohibits the use of the INSERT INTO…SELECT function. This can generate an ERROR and prevent proper Record usage.
Note: You do not need to use any MIN/MAX aggregation on the Child table field in combination with this approach – the Ordering and the First/Last should return the desired row.
Below are screenshots of sample data that correlates to the Record as configured in the preceding sections.
Table4 is LEFT JOINed to Table3, with the additional Data Reduction options as has been shown above.
In the last Results screenshot you can see that we only we only get one Child row from Table4 for each Parent record in Table3, even though we use LEFT JOIN. The Child is chosen based on the configured ordering, which gives us the MIN (oldest) To_Date Child.
Keywords: Altosoft, Insight, data, reduction, join, advanced, options, record