SQL Queries - CAST Management Base - How to get Assessment model details from database

Purpose of Query

This query returns the CAST AIP Assessment model details

Applicable CAST Version

ReleaseYes/No
8.3.x✅

Applicable RDBMS

RDBMSYes/No
CSS4✅
CSS3✅

Query for CSS

with AIPUpgrades as
(
select sph.REVISION_DATE,
       sph.REVISION
  from SYS_PACKAGE_HISTORY sph
where sph.PACKAGE_NAME = 'PMC_MAIN'
)
select distinct th.LOG_DATE,
       tt.TASK_LABEL,
       (select au.REVISION
          from AIPUpgrades au
         where au.REVISION_DATE = (select max(au0.REVISION_DATE)
                                     from AIPUpgrades au0
                                    where au0.REVISION_DATE < th.LOG_DATE)
       ) as UPGRADE_VERSION
  from TASKLOG_HISTORY th
  join TASKLOG_TASKS tt
    on tt.TASK_ID = th.TASK_ID
   and tt.TASK_LABEL like 'Merge Assessment Model%'
where th.LOG_CONTEXT = 'No Task message'
order by th.LOG_DATE;

Query result example

log_datetask_labelupgrade_version
2019-01-04 03:54:10.572Merge Assessment Model [progressive] for xxxx_central*8.3.6.1
2019-03-21 02:25:58.511Merge Assessment Model [progressive] for xxxx_central*8.3.6.1
2019-11-23 03:26:02.136Merge Assessment Model [progressive] for xxxx_central*8.3.16.1
2021-02-21 16:17:31.917Merge Assessment Model [progressive] for xxxx_central*8.3.28.1
2021-04-19 19:38:29.224Merge Assessment Model [conservative] for xxxx_central*8.3.32.1
2021-04-20 09:38:24.57Merge Assessment Model [conservative] for xxxx_central*8.3.33.1
2021-05-04 08:09:02.348Merge Assessment Model [conservative] for xxxx_central*8.3.34.1
2021-05-27 14:44:30.058Merge Assessment Model [conservative] for xxxx_central*8.3.35.1
2021-06-25 09:11:08.417Merge Assessment Model [conservative] for xxxx_central*8.3.36.1
2021-07-28 17:05:46.313Merge Assessment Model [conservative] for xxxx_central*8.3.37.1
2021-09-19 12:04:03.446Merge Assessment Model [conservative] for xxxx_central*8.3.38.1
2021-11-04 23:18:23.302Merge Assessment Model [conservative] for xxxx_central*8.3.39.1
2021-11-22 14:39:26.36Merge Assessment Model [conservative] for xxxx_central*8.3.40.1
2021-12-22 16:13:35.86Merge Assessment Model [conservative] for xxxx_central*8.3.41.1
2022-01-27 06:27:13.1Merge Assessment Model [conservative] for xxxx_central*8.3.42.1
2022-02-24 04:21:47.565Merge Assessment Model [conservative] for xxxx_central*8.3.43.1

Query result interpretation

The query returns the CAST AIP Assessment model details.

The mode of the assessment model migration is given between brackets, with the following meanings:

  • progressive: keep current configuration on existing QRs, activate new QRs
  • conservative: keep current configuration on existing QRs, do not activate new QRs
  • true: replace with the new assessment model
  • retain: do not update current assessment model

Notes/comments

Ticket # 37711

Related Pages