psciascia Honored Contributor.
Honored Contributor.

RE: Cannot find CR in CPC, but present in SQL database

Jump to solution

Alright, I've made some modifications which I've highlighted and at least for my view and state, the results match. I'm sure there will be more exception cases, but I'll adjust . I'm pasting it here with the highlighted differences.

1) added the DISTINCT as per your suggestion

2) added a JOIN to  a sub-select of the latest CR modification in the syn_change table 

3) changed c.EndModifiedTime to vm.EndModifiedTime (nto totally sure the meaning f this column)

Bottom line, it woks for now.  I know that my extra join works for CR's that have been modified to be on another view.

At one point  was joining on the latest modification of the vm  table, but that created other issues, especially for those twho class ID is no longer 'Change'

 

Let me know what you think. I will still mark your answer as the answer because greatly reduced the result set.

 

SELECT
distinct c.ChangeNumber AS [CR #],
p.name AS [Project Name],
v.name AS [View Name],
c.RevisionNumber AS [Revision]
FROM
syn_viewmember vm
INNER JOIN syn_change c ON vm.CurrentObjectID = c.ID and vm.classid = (select id from ctables where stname = 'Change')
INNER JOIN
(
SELECT max(cr.ModifiedTime) as 'ModTime', cr.ChangeNumber as 'CRNum'
FROM syn_Change AS cr
GROUP BY cr.ChangeNumber
) as sub ON (sub.CRNum = c.ChangeNumber AND sub.ModTime = c.ModifiedTime)
INNER JOIN syn_view v ON c.viewid = v.id
INNER JOIN syn_project p ON v.projectid = p.id
where
vm.EndModifiedTime = 0 AND
vm.DeletedTime = 0 AND

ORDER BY
[CR #] ASC 

 

Thanks again,

Patrick

0 Likes
psciascia Honored Contributor.
Honored Contributor.

Re: RE: Cannot find CR in CPC, but present in SQL database

Jump to solution

Thanks James!

I haven't worked directly in SQL for info in a long, long time, but there's a chance I'll get back to it if only to deploys some reports. I make my own reports in HTML now through hte SDK, but they certainly lack the dynamic aspect of SQL reports.  I'll store you updated query for future use.

0 Likes
The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.