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

 

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