krishnamraju

  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.oc

  SQL query to find key fields in a record : SELECT KEYPOSN, FIELDNAME,ASCDESC FROM PSKEYDEFN WHERE RECNAME = 'POSITION_DATA' AND INDEXID = '_' ORDER BY 1; Alternate SQL query to find key fields in a record: SELECT FIELDNAME FROM PSRECFIELD WHERE RECNAME = 'POSITION_DATA' AND BITAND(USEEDIT,1)=1;     SQL Query to find search keys in a record: SELECT FIELDNAME FROM PSRECFIELD WHERE RECNAME = 'POSITION_DATA' AND BITAND(USEEDIT,2048)=2048; SQL Query to list alternate search keys in a record: SELECT FIELDNAME FROM PSRECFIELD WHERE RECNAME = 'POSITION_DATA' AND BITAND(USEEDIT,16)=16; SQL Query to list both the search and alternate search keys of a record: SELECT FIELDNAME FROM PSRECFIELD WHERE RECNAME = 'POSITION_DATA' AND (BITAND(USEEDIT,16)=16 or BITAND(USEEDIT,2048)=2048); SQL Query to get Search Key Fields of a Component Interface: SELECT A.FIELDNAME FROM PSRECFIELD A WHERE A.RECNAME IN ((SELECT B.

  SQL query to get all the child records for a parent record: select rename  from psrecdefn CONNECT BY PRIOR RECNAME=PARENTRECNAME START WITH PARENTRECNAME = :1 -- Parent Record name This SQL will help in analyzing  whenever we have to delete data in a record and to retrieve all the child definitions of a record. However, this will work only for records which have a parent record specified in record properties.