Geeks on Campus

Another amazing bgsu blog

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.

 

Leave a Reply

Your email address will not be published. Required fields are marked *