Check What Record Has Your Fields

No votes yet

This is another quick SQL snippet that I find useful. When would you use it?
Well, you forgot a certain record name, but you know that the record has EMPLID, NAME, EMPL_REC fields on it.
You can query the database to find out what records have the fields you are looking for.

SELECT RECNAME, COUNT(FIELDNAME)
FROM PSRECFIELDALL
WHERE FIELDNAME IN ('EMPLID', 'NAME', 'EMPL_RCD')
GROUP BY RECNAME
HAVING COUNT(FIELDNAME) = 3;

Please try to help out with unanswered topics on the forum. Chances are you have had the same issue/question some time in your IT career!

Comments

Have a question? Please ask it on the forum instead.
kpstechie's picture
User offline. Last seen 5 years 10 weeks ago. Offline
Joined: 02/18/2009
Posts: 3
Re: Check What Record Has Your Fields

Here is a similar sql but I use it for different puporse. I use this to find out the record mappings of a field while designing the application.

To find the list of records that has specific field in that record. Useful in finding out mappings.

SELECT A.RECNAME, A.FIELDNAME, B.DESCRLONG AS RECORDDESCRIPTION
FROM PSRECFIELD A, PSRECDEFN B
WHERE A.FIELDNAME IN( 'FIELD_NAME' ,'FIELD_NAME' , 'FIELD_NAME' )
AND A.RECNAME = B.RECNAME
AND A.RECNAME NOT LIKE '%WRK%'
AND A.RECNAME LIKE 'TL%'
AND A.RECNAME NOT LIKE '%TAO'
AND A.RECNAME NOT LIKE '%TMP'
AND A.RECNAME NOT LIKE '%SRCH'
AND A.RECNAME NOT LIKE '%SBR'

Post new comment

CAPTCHA
The question below is to prevent automated spam submissions.
16 + 4 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.