Searching for versions across multiple projects

Products

• CA Harvest Software Change Manager
• CA Harvest Software Change Manager - OpenMake Meister

Issue/Introduction

Is there any way to search for all versions of an item across multiple projects?

Or do you have to search project by project?

Environment

Harvest Software Change Manager all versions

Resolution

This is an example of a query that is flexible about the search criteria.  Read the instructions below in order to customize the query to your needs.

SELECT DISTINCT HARENVIRONMENT.ENVIRONMENTNAME,
  HARENVIRONMENT.ENVISACTIVE,
  HARSTATE.STATENAME,
  HARPACKAGE.PACKAGENAME,
  HARPATHFULLNAME.PATHFULLNAME,
  HARITEMNAME.ITEMNAME,
  HARVERSIONS.MAPPEDVERSION,
  HARVERSIONS.VERSIONSTATUS
FROM HARENVIRONMENT
INNER JOIN HARVIEW ON HARENVIRONMENT.ENVOBJID = HARVIEW.ENVOBJID
INNER JOIN HARVERSIONINVIEW ON HARVIEW.VIEWOBJID = HARVERSIONINVIEW.VIEWOBJID
INNER JOIN HARVERSIONS ON HARVERSIONS.VERSIONOBJID = HARVERSIONINVIEW.VERSIONOBJID
INNER JOIN HARPATHFULLNAME ON HARVERSIONS.PATHVERSIONID = HARPATHFULLNAME.VERSIONOBJID
INNER JOIN HARITEMNAME ON HARITEMNAME.NAMEOBJID = HARVERSIONS.ITEMNAMEID
LEFT JOIN HARPACKAGE ON HARPACKAGE.PACKAGEOBJID = HARVERSIONS.PACKAGEOBJID
LEFT JOIN HARSTATE ON HARSTATE.STATEOBJID = HARPACKAGE.STATEOBJID
WHERE 
/* HARENVIRONMENT.ENVIRONMENTNAME = 'SampleProject' AND                        /* if you want to search in only one project */
/* HARENVIRONMENT.ENVIRONMENTNAME IN ('SampleProject1', 'SampleProject2') AND  /* if you want to search in a list of projects */
/* HARENVIRONMENT.ENVIRONMENTNAME LIKE 'Sample%' AND                           /* if you want to wildcard-search across projects */
/* HARPATHFULLNAME.PATHFULLNAME = '\SampleRepository' AND                            /* if you want to search in only one viewpath */
/* HARPATHFULLNAME.PATHFULLNAME IN ('\SampleRepository1', '\SampleRepository2') AND  /* if you want to search in a list of viewpaths */
/* HARPATHFULLNAME.PATHFULLNAME LIKE '\Sample%' AND                                  /* if you want to wildcard-search across viewpaths */
/* HARITEMNAME.ITEMNAME = 'README.TXT' AND                     /* if you want to search in only one itemname */
/* HARITEMNAME.ITEMNAME IN ('README.TXT1', 'README.TXT2') AND  /* if you want to search in a list of itemnames */
/* HARITEMNAME.ITEMNAME LIKE 'Sample%' AND                     /* if you want to wildcard-search across itemnames */
 HARENVIRONMENT.ENVOBJID  > 0 AND  /* DO NOT comment out this line */
 HARENVIRONMENT.ENVISACTIVE = 'Y'  /* DO NOT comment out this line */

 

The WHERE clause is set up to easily customize the query for a number of different possible filters.  Uncomment the desired types of filters and provide the values you're looking for enclosed in single quotes.  Be sure to use the proper capitalization, just as the value is stored in the database.  Make sure the "AND" at the end of the statement (and before the comment) does not get lost.

 

If you want to search for a specific value, uncomment the row for the type of value containing the "=" operator and provide the value for which you're looking 

If you want to search for a list of specific, uncomment the row for the type of value containing the "IN" operator and provide the values for which you're looking

If you want to wildcard-search for similar values, uncomment the row for the type of value containing the "LIKE" operator provide the value for which you're looking including wildcards.  In Oracle, a single-character wildcard is the question mark "?" and a multiple-character wildcard is the percent "%"

 

To uncomment a row, simply remove "/*" from the beginning of that row.