Latest revision of each view report

Forum|Forum|5 years ago
June 19, 2020
2 replies
4299 views

We typically use this something like this to get the latest rev either in reports or sql.

"p.versionsortida2versioninfo = (select max (p1.versionsortida2versioninfo) from wcadmin.wtpart p1 where p1.ida3masterreference = p.ida3masterreference)
and p.latestiterationinfo = 1"

Does anyone know how to make this work for each view? The issue comes up when the view revisions are not the same. For example "part1 Rev A Design view" and "part1 Rev B Service view" excludes the design view. Anyone know a trick to getting this to work?

Best answer by joe_morton

Hi,

I've been developing/experimenting with a lot of report templates (not the SQL directly though). I'll try to describe it as best I can.

When you do a max condition, normally you would get just a single result. If you want to get a max for each of something, then that something has to be defined in the main query, and the subselect has a criteria that goes back to the main query.

So basically in your subquery, you would add a criteria that the view = the view of the WTpart from the main query. That way, you should get a result for the max of each view of each WTPart.

joe_morton

Answer

18-Opal

Hi,

I've been developing/experimenting with a lot of report templates (not the SQL directly though). I'll try to describe it as best I can.

When you do a max condition, normally you would get just a single result. If you want to get a max for each of something, then that something has to be defined in the main query, and the subselect has a criteria that goes back to the main query.

So basically in your subquery, you would add a criteria that the view = the view of the WTpart from the main query. That way, you should get a result for the max of each view of each WTPart.