Need sql to list all recent package promotions

Document ID:  TEC1644877
Last Modified Date:  07/03/2017
{{active ? 'Hide' : 'Show'}} Technical Document Details

Products

  • CA Harvest Software Change Manager

Releases

  • CA Harvest Software Change Manager:Release:12.5
  • CA Harvest Software Change Manager:Release:12.6
  • CA Harvest Software Change Manager:Release:13.0

Components

  • CA Harvest Software Change Manager:CCCHV
Question:

I need to create a query on Harvest SCM tables that allow me to extract all the promotions made in test testing and production of the different projects I manage

Answer:

If your database is Oracle, try this:

SELECT HARPKGHISTORY.ENVIRONMENTNAME,
HARPKGHISTORY.STATENAME,
HARPACKAGE.PACKAGENAME,
HARPKGHISTORY.ACTION,
TO_CHAR(HARPKGHISTORY.EXECDTIME, 'mm-dd-yyyy hh:mi:ss') EXECDTIME,
HARALLUSERS.USERNAME
FROM HARPKGHISTORY
INNER JOIN HARPACKAGE
ON HARPKGHISTORY.PACKAGEOBJID = HARPACKAGE.PACKAGEOBJID
INNER JOIN HARALLUSERS
ON HARPKGHISTORY.USROBJID = HARALLUSERS.USROBJID
WHERE ACTION = 'Promote'
AND ENVIRONMENTNAME IN ('SampleProject', 'TestConcurrentMerge') /* <--- List the names of your projects here */
AND STATENAME IN ('Development', 'Test') /* <--- List the names of your states here */
AND EXECDTIME BETWEEN TO_DATE('04-05-2017', 'mm-dd-yyyy') /* <--- List the beginning date of your date range here */
AND TO_DATE('04-20-2017', 'mm-dd-yyyy') /* <--- List the ending date of your date range here */

If your database is SQL Server, try this:
SELECT HARPKGHISTORY.ENVIRONMENTNAME,
HARPKGHISTORY.STATENAME,
HARPACKAGE.PACKAGENAME,
HARPKGHISTORY.ACTION,
HARPKGHISTORY.EXECDTIME,
HARALLUSERS.USERNAME
FROM HARPKGHISTORY
INNER JOIN HARPACKAGE
ON HARPKGHISTORY.PACKAGEOBJID = HARPACKAGE.PACKAGEOBJID
INNER JOIN HARALLUSERS
ON HARPKGHISTORY.USROBJID = HARALLUSERS.USROBJID
WHERE ACTION = 'Promote'
AND ENVIRONMENTNAME IN ('SampleProject', 'TestConcurrentMerge') /* <--- List projects here */
AND STATENAME IN ('Development', 'Test') /* <--- List states here */
AND EXECDTIME BETWEEN '04-05-2017' /* <--- List beginning of date range here */
AND '04-22-2017' /* <--- List ending of date range here */

Depending on your settings in Windows and in Oracle or SQL Server you may have to adjust the formats of your dates accordingly.

Please help us improve!

Will this information enable you to resolve your issue?

Please tell us what we can do better.

{{feedbackText.length ? feedbackText.length : '0'}}/255

{{status}}

Not what you were looking for?

Search Again >

Product Information

Support by Product >

Communities

Join a Community >