Ask Question
Help Others
Home › Blogs › Lepa's blog

Check What Record Has Your Fields

Tue, 01/15/2008 - 11:54pm — Lepa
Your rating: None Average: 2 (1 vote)

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;

Bookmark/Search this post with
  • Buzz Up!
  • del.icio.us
  • Digg
  • Facebook
  • Google Buzz
  • Reddit
  • StumbleUpon
  • Technorati
  • Twitter
  • Yahoo
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.
Thu, 02/19/2009 - 2:24pm
#1
kpstechie's picture
kpstechie
User offline. Last seen 8 years 12 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'

Top