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

  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.