The most common type of error when generating a snapshot is to ignore rule 3 and/or 4considerations pertaining to dates and active status. For example, it is reasonable to want to filter a view so that only active components are included. However, if you apply that filter in a way that restricts the substrate from which the snapshot is generated you will get incorrect results in which all components appear to be active even after they have been inactivated. An example of this error is shown on has been presented below.
| Caption label |
|---|
| CapId | full-release-as-at-2019-01-31 |
|---|
| CapType | Table |
|---|
|
Full Release as at 2019-01-31 |
| id | effectiveTime | active | value |
|---|
| A | 20170131 | 1 | Red |
| B | 20170131 | 1 | Amber |
| C | 20170131 | 1 | Yellow |
| A | 20180131 | 0 | Red |
| B | 20180131 | 1 | Orange |
| D | 20180131 | 1 | Green |
| B | 20190131 | 0 | Orange |
| E | 20190131 | 1 | Blue |
The following query when applied to the full release data shown in generates in
| Caption reference |
|---|
| CapRefId | full-release-as-at-2019-01-31 |
|---|
| CapRefType | Table |
|---|
|
generates the erroneous snapshot view shown in
| Caption reference |
|---|
| CapRefId | erroneous-snapshot-view-as-at-2019-01-31 |
|---|
| CapRefType | Table |
|---|
|
. This query excludes inactive rows at the same time as identifying the most recent effectiveTime. The result is that components A and B are shown as active because the later rows that inactivated those components were excluded.
| Code Block |
|---|
Select * from `component` `c` where
`c`.`effectiveTime`=(Select max(effectiveTime) from `component` where `id`=`c`.`id` and active=1) |
| Caption label |
|---|
| CapId | erroneous-snapshot-view-as-at-2019-01-31 |
|---|
| CapType | Table |
|---|
|
Erroneous Snapshot view as at 2019-01-31 |
| id | effectiveTime | active | value |
|---|
| A | 20170131 | 1 | Red |
| C | 20170131 | 1 | Yellow |
| B | 20180131 | 1 | Orange |
| D | 20180131 | 1 | Green |
| E | 20190131 | 1 | Blue |
The following query when applied to the full release data shown in generates in
| Caption reference |
|---|
| CapRefId | full-release-as-at-2019-01-31 |
|---|
| CapRefType | Table |
|---|
|
generates the correct snapshot view shown in
| Caption reference |
|---|
| CapRefId | correct-snapshot-view-as-at-2019-01-31 |
|---|
| CapRefType | Table |
|---|
|
. The most recent versions of all the components A-E are included. In the case of components A and B these are both inactive.
...
| Code Block |
|---|
Select * from `component` `c`
where `c`.`effectiveTime`=(Select max(effectiveTime) from `component` where `id`=`c`.`id`) |
| Caption label |
|---|
| CapId | correct-snapshot-view-as-at-2019-01-31 |
|---|
| CapType | Table |
|---|
|
Correct Snapshot view as at 2019-01-31 |
| id | effectiveTime | active | value |
|---|
| C | 20170131 | 1 | Yellow |
| A | 20180131 | 0 | Red |
| D | 20180131 | 1 | Green |
| B | 20190131 | 0 | Orange |
| E | 20190131 | 1 | Blue |
The following query when applied to the full release data shown in generates in
| Caption reference |
|---|
| CapRefId | full-release-as-at-2019-01-31 |
|---|
| CapRefType | Table |
|---|
|
generates the correct snapshot view shown
in with in | Caption reference |
|---|
| CapRefId | correct-snapshot-view-as-at-2019-01-31-with-inactive-rows-excluded |
|---|
| CapRefType | Table |
|---|
|
with inactive rows filtered out after generating the snapshot view. Only components C-E are included as the most recent versions of components A and B are inactive.
...
| Code Block |
|---|
Select * from `component` `c`
where `c`.`active`=1
and `c`.`effectiveTime`=(Select max(effectiveTime) from `component` where `id`=`c`.`id`) |
| Caption label |
|---|
| CapId | correct-snapshot-view-as-at-2019-01-31-with-inactive-rows-excluded |
|---|
| CapType | Table |
|---|
|
Correct Snapshot view as at 2019-01-31 with inactive rows excluded |
| id | effectiveTime | active | value |
|---|
| C | 20170131 | 1 | Yellow |
| D | 20180131 | 1 | Green |
| E | 20190131 | 1 | Blue |