PS Query LAG Function
Posted by jeggent on November 4, 2010
PeopleSoft Query Tip of the Week.
Disclaimer: This may only work with Oracle databases
Did you know that you can use the LAG function in an expression to reference a field in a previous row? It’s true! I’ll give you a useful example of this functionality.
Expression Text
CASE WHEN LAG(A.EMPLID,1) OVER (ORDER BY A.EMPLID) = A.EMPLID THEN ‘N’
ELSE ‘Y’
END
In English: When this EMPLID is equal to the previous EMPLID then the result of this expression is ‘N’ (not unique), otherwise ‘Y’ (it is unique).
You can now use this expression as a field to display a ‘Y’ when the EMPLID changes or an ‘N’ when it is the same as the previous row. Alternatively you could output a 1 or 0 (zero) which could then be summed to get a count of the unique IDs in the results. This is useful for me when I am listing, for example, all of the awards a student has. The query is returning multiple award lines per ID. I can use this expression to get a unique count of students on the listing.
Another tool for your PS Query toolbox. Enjoy.