SQL Query to identify the Level 0 record of a component
The below SQL will give all the records of a component along with its corresponding occurs levels:
SELECT DISTINCT D.recname TableName,
D.occurslevel ScrollName
FROM pspnlgrpdefn A,
pspnlgroup B,
pspnldefn C,
pspnlfield D,
psrecdefn E
WHERE A.pnlgrpname = B.pnlgrpname
AND A.market = B.market
AND B.pnlname = C.pnlname
AND C.pnlname = D.pnlname
AND A.pnlgrpname = 'POSITION_DATA' --Component Name
AND E.recname = D.recname
/* Restricts to only tables and eliminates views and derived records */
AND E.rectype IN ( 0 )
/*This condition eliminates records in component as Related Fields*/
AND To_char(Bitand(D.fielduse, 16)) <> '16'
ORDER BY 2,1
D.occurslevel ScrollName
FROM pspnlgrpdefn A,
pspnlgroup B,
pspnldefn C,
pspnlfield D,
psrecdefn E
WHERE A.pnlgrpname = B.pnlgrpname
AND A.market = B.market
AND B.pnlname = C.pnlname
AND C.pnlname = D.pnlname
AND A.pnlgrpname = 'POSITION_DATA' --Component Name
AND E.recname = D.recname
/* Restricts to only tables and eliminates views and derived records */
AND E.rectype IN ( 0 )
/*This condition eliminates records in component as Related Fields*/
AND To_char(Bitand(D.fielduse, 16)) <> '16'
ORDER BY 2,1
Note: Adding condition as d.occurslevel = 0 will return only the level 0 record
Good post. Really usefull.mypeoplesoft
ReplyDelete