SQL Query all Records with PeopleSoft Component

JointLantic

3 years ago

SQL Query for Retrieve all Records associated with a PeopleSoft Component

SELECT R.RECNAME AS RECORD_NAME,
       ( CASE
           WHEN R.RECTYPE = 0 THEN 'Table'
           WHEN R.RECTYPE = 1 THEN 'View'
           WHEN R.RECTYPE = 2 THEN 'Derived'
           WHEN R.RECTYPE = 3 THEN 'Sub Record'
           WHEN R.RECTYPE = 5 THEN 'Dynamic View'
           WHEN R.RECTYPE = 6 THEN 'Query View'
           WHEN R.RECTYPE = 7 THEN 'Temporary Table'
           ELSE 'Unknown'
         END )   AS RECORD_TYPE
FROM   PSRECDEFN R
WHERE  R.RECNAME IN (SELECT DISTINCT RECNAME
                     FROM   PSPNLFIELD
                     WHERE  PNLNAME IN (SELECT DISTINCT B.PNLNAME
                                        FROM   PSPNLGROUP A,
                                               PSPNLFIELD B
                                        WHERE  ( A.PNLNAME = B.PNLNAME
                                                  OR A.PNLNAME = B.SUBPNLNAME )
                                           AND A.PNLGRPNAME='JOB_DATA' /*Component Name*/
                                           AND RECNAME <> ' ')
                     UNION
                     SELECT DISTINCT RECNAME
                     FROM   PSPNLFIELD
                     WHERE  PNLNAME IN (SELECT DISTINCT B.SUBPNLNAME
                                        FROM   PSPNLGROUP A,
                                               PSPNLFIELD B
                                        WHERE  ( A.PNLNAME = B.PNLNAME
                                                  OR A.PNLNAME = B.SUBPNLNAME )
                                           AND A.PNLGRPNAME='JOB_DATA'/*Component Name*/
                                           ))
   AND R.RECNAME <> ' '
ORDER  BY R.RECTYPE ;

SELECT R.RECNAME AS RECORD_NAME,

( CASE

WHEN R.RECTYPE = 0 THEN 'Table'

WHEN R.RECTYPE = 1 THEN 'View'

WHEN R.RECTYPE = 2 THEN 'Derived'

WHEN R.RECTYPE = 3 THEN 'Sub Record'

WHEN R.RECTYPE = 5 THEN 'Dynamic View'

WHEN R.RECTYPE = 6 THEN 'Query View'

WHEN R.RECTYPE = 7 THEN 'Temporary Table'

ELSE 'Unknown'

END ) AS RECORD_TYPE

FROM PSRECDEFN R

WHERE R.RECNAME IN (SELECT DISTINCT RECNAME

FROM PSPNLFIELD

WHERE PNLNAME IN (SELECT DISTINCT B.PNLNAME

FROM PSPNLGROUP A,

PSPNLFIELD B

WHERE ( A.PNLNAME = B.PNLNAME

OR A.PNLNAME = B.SUBPNLNAME )

AND A.PNLGRPNAME='JOB_DATA' /*Component Name*/

AND RECNAME <> ' ')

UNION

SELECT DISTINCT RECNAME

FROM PSPNLFIELD

WHERE PNLNAME IN (SELECT DISTINCT B.SUBPNLNAME

FROM PSPNLGROUP A,

PSPNLFIELD B

WHERE ( A.PNLNAME = B.PNLNAME

OR A.PNLNAME = B.SUBPNLNAME )

AND A.PNLGRPNAME='JOB_DATA'/*Component Name*/

))

AND R.RECNAME <> ' '

ORDER BY R.RECTYPE ;

Result SQL

SELECT DISTINCT cp.pnlgrpname,  
                cp.market,
                cp.descr,
                pgf.recname,
                r.recdescr,
                r.rectype FROM  pspnlgrpdefn cp,
       pspnlgroup cpg,
       pspnldefn pg,
       pspnlfield pgf,
       psrecdefn r
WHERE cp.pnlgrpname = 'JOB_DATA' /* Component Name*/
       AND cp.market = 'GBL'
       AND cp.pnlgrpname = cpg.pnlgrpname
       AND cp.market = cp.market
       AND pg.pnlname = cpg.pnlname
       AND pgf.pnlname = pg.pnlname
       AND pgf.recname = r.recname;

SELECT DISTINCT cp.pnlgrpname,

cp.market,

cp.descr,

pgf.recname,

r.recdescr,

r.rectype FROM pspnlgrpdefn cp,

pspnlgroup cpg,

pspnldefn pg,

pspnlfield pgf,

psrecdefn r

WHERE cp.pnlgrpname = 'JOB_DATA' /* Component Name*/

AND cp.market = 'GBL'

AND cp.pnlgrpname = cpg.pnlgrpname

AND cp.market = cp.market

AND pg.pnlname = cpg.pnlname

AND pgf.pnlname = pg.pnlname

AND pgf.recname = r.recname;

Result query

Reference : https://hmahanta.blogspot.com/2021/07/useful-quesries-in-peoplesoft.html