Query to get the latest version of a record only if the record state is unconfirmed
Article ID: KB0077134
Updated On:
| Products | Versions |
|---|---|
| TIBCO MDM | - |
Description
Consider below scenarios:
S.no ID# Version# Record State
1 R1 1 Unconfirmed
2 R1 2 Unconfirmed
3 R1 3 Unconfirmed
4 R1 4 Unconfirmed
In the above scenario, the query should return only the 4th version.
Scenario:2
S.no ID# Version# Record State
1 R1 1 Unconfirmed
2 R1 2 Unconfirmed
3 R1 3 Unconfirmed
4 R1 4 Confirmed
In the above scenario, the query should not return any result as the latest record is the confirmed version.
Scenario:3
S.no ID# Version# Record State
1 R1 1 Unconfirmed
2 R1 2 Unconfirmed
3 R1 3 Confirmed
4 R1 4 Unconfirmed
In the above scenario, the query should return only the 4th version which is unconfirmed.
S.no ID# Version# Record State
1 R1 1 Unconfirmed
2 R1 2 Unconfirmed
3 R1 3 Unconfirmed
4 R1 4 Unconfirmed
In the above scenario, the query should return only the 4th version.
Scenario:2
S.no ID# Version# Record State
1 R1 1 Unconfirmed
2 R1 2 Unconfirmed
3 R1 3 Unconfirmed
4 R1 4 Confirmed
In the above scenario, the query should not return any result as the latest record is the confirmed version.
Scenario:3
S.no ID# Version# Record State
1 R1 1 Unconfirmed
2 R1 2 Unconfirmed
3 R1 3 Confirmed
4 R1 4 Unconfirmed
In the above scenario, the query should return only the 4th version which is unconfirmed.
Issue/Introduction
Query to get the latest version of a record only if the record state is unconfirmed
Environment
Product: TIBCO MASTER DATA MANAGEMENT
Version: ALL
OS: Windows
Resolution
To get all possible outputs for the above scenario's, use the below query:
==================
WITH GU AS (
SELECT PRODUCTKEYID PKEY, CATALOGID CATID, MAX(MODVERSION) MAXVERSION
FROM PRINCIPALKEY
WHERE STATE = 'UNCONFIRMED'
GROUP BY PRODUCTKEYID, CATALOGID
),
GA AS (
SELECT PRODUCTKEYID PKEY, CATALOGID CATID, MAX(MODVERSION) MAXVERSION
FROM PRINCIPALKEY
GROUP BY PRODUCTKEYID, CATALOGID
)
SELECT P.COLUMN_NAME, P.COLUMN_NAME, P.COLUMN_NAME, GU.PKEY, GU.CATID, P.CPRODUCTID, P.CPRODUCTIDEXT, GU.MAXVERSION, PKEY.STATE
FROM GU
INNER JOIN GA ON GA.PKEY=GU.PKEY AND GA.CATID=GU.CATID AND GA.MAXVERSION=GU.MAXVERSION
INNER JOIN PRINCIPALKEY PKEY ON PKEY.PRODUCTKEYID=GU.PKEY AND PKEY.CATALOGID=GU.CATID AND PKEY.MODVERSION=GU.MAXVERSION
INNER JOIN <TARGETED_REPOSITORY>
P ON P.CPRODUCTKEYID=GU.PKEY AND P.CMODVERSION=GU.MAXVERSION;
====================
Where
1. TARGETED_REPOSITORY is the repository/catalog name from which you want the records with the latest UNCONFIRMED state.
2. COLUMN_NAME is the attribute names that you want to display while returning latest UNCONFIRMED record.
==================
WITH GU AS (
SELECT PRODUCTKEYID PKEY, CATALOGID CATID, MAX(MODVERSION) MAXVERSION
FROM PRINCIPALKEY
WHERE STATE = 'UNCONFIRMED'
GROUP BY PRODUCTKEYID, CATALOGID
),
GA AS (
SELECT PRODUCTKEYID PKEY, CATALOGID CATID, MAX(MODVERSION) MAXVERSION
FROM PRINCIPALKEY
GROUP BY PRODUCTKEYID, CATALOGID
)
SELECT P.COLUMN_NAME, P.COLUMN_NAME, P.COLUMN_NAME, GU.PKEY, GU.CATID, P.CPRODUCTID, P.CPRODUCTIDEXT, GU.MAXVERSION, PKEY.STATE
FROM GU
INNER JOIN GA ON GA.PKEY=GU.PKEY AND GA.CATID=GU.CATID AND GA.MAXVERSION=GU.MAXVERSION
INNER JOIN PRINCIPALKEY PKEY ON PKEY.PRODUCTKEYID=GU.PKEY AND PKEY.CATALOGID=GU.CATID AND PKEY.MODVERSION=GU.MAXVERSION
INNER JOIN <TARGETED_REPOSITORY>
P ON P.CPRODUCTKEYID=GU.PKEY AND P.CMODVERSION=GU.MAXVERSION;
====================
Where
1. TARGETED_REPOSITORY is the repository/catalog name from which you want the records with the latest UNCONFIRMED state.
2. COLUMN_NAME is the attribute names that you want to display while returning latest UNCONFIRMED record.
Feedback
Yes
No
Powered by
