Ask Question
Help Others
Home › PeopleSoft › PeopleSoft SQL Snippets

Identify Records with a Specific Subrecord

Wed, 09/17/2008 - 9:08am — Lepa
Your rating: None Average: 5 (2 votes)

A subrecord in PeopleSoft is a way to group fields together and place them on a record, view, or a temp table. Here is a query you can use to identify records that have a specific subrecord.

SELECT a.recname, a.fieldname, b.rectype, b.objectownerid
  FROM psrecfield a, psrecdefn b
 WHERE a.recname = b.recname
   AND a.fieldname = 'SUB-RECORD-NAME';

When you modify a subrecord in Peopltools as for example adding a field to it. Make sure you modify (re-build, or alter) all the tables that the subrecord touches. You can use the SQL above to track down all the affected record but you really only want actual physical tables, views and temp tables. So, we will add rectype criteria to filter our results.

SELECT   a.recname, a.fieldname, b.rectype, b.objectownerid
    FROM psrecfield a, psrecdefn b
   WHERE a.recname = b.recname
     AND a.fieldname = 'SUB-RECORD-NAME'
    -- 0 table, 1 view, 7 temp table
     AND b.rectype IN (0, 1, 7)
ORDER BY a.recname;

Bookmark/Search this post with:
  • Delicious
  • Digg
  • StumbleUpon
  • Reddit
  • Furl
  • Facebook
  • Google
  • Yahoo
  • Technorati
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.

Post new comment

The content of this field is kept private and will not be shown publicly.
CAPTCHA
The question below is to prevent automated spam submissions.
1 + 0 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.