Reporting out current folder owner

Jennifer Methlie
Jennifer Methlie
in AppWorks #1

I'm trying to provide a report with all folders in certain stages and want to show who currently is working on the folder (our folders move thru the process and may wait in a queue stage that multiple people can then "pick up" to action).   Since the actions may take days, it's helpful to see which are being actioned by whom without having to open the folder.

 

I think I need to query the eevent table and get the eusername with the largest eeventid but something's not right.  The query (for just one folder) is

 

select e.eusername, e.eeventid, e.efolderid
from eevent e,
(select  efolderid,  max(eeventid) as MaxID
 from eevent
 group by efolderid) CurrentDoer
 where e.efolderid = '0900000000000000000000000000773'
 and e.eeventid = CurrentDoer.MaxID

 

but instead of returning a single user, I still get multiple lines.   Any help would be appreciated.  Thanks!

Tagged:

Comments

  • Jerome_Pearce
    Jerome_Pearce
    #2

    I think you want the folowing SQL:

     

    select e.eusername, e.eeventid, e.efolderid
    from eevent e
    WHERE e.efolderid = '0900000000000000000000000000773'
    AND eEventId = (select  max(eeventid) from eEvent WHERE efolderid = e.eFolderId )

  • Brian Mellert
    Brian Mellert Member
    #3

    Or perhaps (if using SQL Server anyway)

     

    SELECT TOP 1 eUserName

    FROM eEvent

    WHERE eFolder = '09............0773'

    ORDER BY eEventID DESC

  • Jennifer Methlie
    Jennifer Methlie
    #4

    Thanks!  That worked.  We're running Oracle so the "Top 1" didn't.  

Categories

image
OpenText CE Products
AppWorks
Gateway & Platform