Queries to get the Latest CONFIRMED and UNCONFIRMED records

Queries to get the Latest CONFIRMED and UNCONFIRMED records

book

Article ID: KB0077641

calendar_today

Updated On:

Products Versions
TIBCO MDM 9.0.1 and 9.1.0

Description

This article helps to get/retrieve the records with latest CONFIRMED and UNCONFIRMED version of the records.

Issue/Introduction

This article provide queries to retrieve latest CONFIRMED and UNCONFIRMED records in a particular catalog

Environment

All

Resolution

The below query shows the latest unconfirmed records in a particular catalog. Here MCT_34307 is the catalog table.
Select B.* from (SELECT PRODUCTKEYID, MAX(MODVERSION) MODVERSION
FROM PRINCIPALKEY where state = 'UNCONFIRMED' and CATALOGID = '34307'
GROUP BY PRODUCTKEYID) A, MCT_34307 B where A.PRODUCTKEYID = B.CPRODUCTKEYID and A.MODVERSION = B.CMODVERSION;

To see all the latest confirmed records in a particular catalog, we need to query goldencopy table. Here MCT_34307 is the catalog table. Please find below the query.
SELECT mct.*
FROM goldencopy gc, MCT_34307 mct
WHERE (mct.cproductkeyid = gc.productkey and mct.cmodversion = gc.version);
Powered by Wolken Software