11/24/12

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.

Workflow errors on New Hire and Add employment instance

User tries to hire a new person or tries to add a new organization relationship encounters a series of error messages related to workflow...

Trending in this Blog this month