11/24/12

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 



Note: Adding condition as d.occurslevel = 0 will return only the level 0 record

1 comment:

Please refrain for marketing messages and unnecessary back links.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month