Header Ads

  • Recent Post

    SQL Queries to find Key, Search Key, Alternate Search Keys of a record and query to get Search Key fields of a CI

    The below specified queries are no big deal as we can retrieve the information by viewing the respective definition in Application Designer. However, I have faced challenges to retrieve the information immediately for some analysis only to find that without access to Application Designer. Hence created these queries for my reference.


    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.SEARCHRECNAME FROM PSBCDEFN B WHERE B.BCNAME =:1) UNION
    (SELECT B.SEARCHRECNAME FROM PSBCDEFN B WHERE B.BCNAME = :1)) AND BITAND(A.USEEDIT,2048)=2048




    1 comment:

    Please refrain for marketing messages and unnecessary back links.

    Post Top Ad

    Post Bottom Ad