Header Ads

  • Recent Posts

    SQL Query to fetch list of all processes\jobs in a recurrence

    One of my colleague asked me for a sql query to retrieve the list of all processes and jobs for a particular recurrence. I wrote the below SQL which gives a list of PeopleSoft processes running in Process Scheduler for a recurrence. This SQL also gives the individual processes associated with a PS Job scheduled in the same recurrence.
    \


    SELECT process_job_name, 
           
    description, 
           
    recurname 
    FROM   ps_prcsrecur a, 
           
    (SELECT p.prcsname  AS Process_Job_name, 
                   
    p.descr     AS Description, 
                   
    p.recurname AS recurname 
            
    FROM   ps_prcsdefn p 
            
    UNION 
            
    SELECT jp.prcsjobname AS Process_Job_name, 
                   
    (SELECT j.descr 
                    
    FROM   ps_prcsdefn j 
                    
    WHERE  j.prcsname = jp.prcsname) AS Description, 
                   
    jp.recurname AS recurname 
            
    FROM   ps_prcsjobdefn j, 
                   
    ps_prcsjobitem jp 
            
    WHERE  j.prcsjobname = jp.prcsjobname) b
    WHERE b.recurname = a.recurname 
    AND a.recurname = :1
    ORDER  BY 1

    3 comments:

    1. I'm getting this error

      ORA-00904: "JP"."RECURNAME": invalid identifier
      00904. 00000 - "%s: invalid identifier"
      *Cause:
      *Action:
      Error at Line: 13 Column: 4

      ReplyDelete
    2. line 13: select SELECT jp.prcsjobname ,its wrongly printed as prcsjobame

      ReplyDelete

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad