Header Ads

  • Recent Posts

    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.

    Post Top Ad

    Post Bottom Ad