E9.2 Find versions are in DV but not PY and that are newer in DV than PY on iSeries

[njcncadmin]

I'm looking for a way via SQL on an iSeries to do two things. First, identify the versions that are in DV but not in PY. Second, identify the versions in DV that are newer than in PY based on check in date. Anyone have something that does this or similar? Thanks

 

[ice_cube210]

Hi There, try these queries. Use Ops Navigator. If you will use the green screen then re-format accordingly.

--Query to find versions in DV that do not exist in PY. Change Library name according to E1 Version. Below example assumes 9.0

select DV.VRPID, DV.VRVERS FROM CODV900.F983051 DV WHERE
DV.VRVERS NOT IN (SELECT VRVERS FROM COPY900.F983051 WHERE VRPID = DV.VRPID);


For finding versions in DV that have a newer date than PY use the VRVCD field which gets updated when you - check in a version, promote a version (gets updated in target), or change a processing option value. So it's NOT ONLY where the check in date is newer. This also won't account for scenarios where DV and PY were updated on the same day but the time was different.


SELECT DV.VRPID, DV.VRVERS, DV.VRJD, DV.VRVCD AS "DV Change Date", PY.VRVCD AS "PY Change Date" FROM CODV900.F983051 DV , COPY900.F983051 PY WHERE DV.VRPID = PY.VRPID AND DV.VRVERS = PY.VRVERS AND DV.VRVCD > PY.VRVCD;

If you want to list only where an actual check in happened in DV newer than PY then try this

SELECT DV.VRPID , DV.VRVERS , DV.VRJD , DV.VRCHKOUTDAT AS "DV Change Date" , PY.VRCHKOUTDAT AS "PY Change Date" FROM CODV900.F983051 DV , COPY900.F983051 PY
WHERE DV.VRPID = PY.VRPID AND DV.VRVERS = PY.VRVERS AND DV.VRCHKOUTSTS = 'N' AND DV.VRCHKOUTDAT > PY.VRCHKOUTDAT;

Please validate all query results as I have not cross-verified.