Ask Question
Help Others
Home › PeopleSoft › PeopleSoft SQL Snippets

Get Edit Tables Behind a Record Fields

Sun, 12/14/2008 - 4:05pm — Lepa
Your rating: None Average: 4 (2 votes)

A straight forward SQL to get edit tables behind field(s) for a specific record. The SQL execludes any edit tables that start with "%" as those are dynamic edit tables with values populated by PeopleCode at run time and thus will not be of a good use in this query.

SELECT R.FIELDNAME 
 , R.EDITTABLE 
  FROM PSRECFIELDDB R 
  , PSDBFIELD F 
 WHERE R.RECNAME = :RecordName
   AND SUBSTR(R.EDITTABLE,1,1) <> '%'  
   AND R.EDITTABLE <> ' '  
   AND R.FIELDNAME = F.FIELDNAME
   AND F.FLDNOTUSED = 0;

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.
Wed, 02/04/2009 - 8:04pm
#1
Jason G's picture
Jason G (not verified)
What is the purpose of PSDBFIELD.FLDNOTUSED ?

It's been bugging me for a while, do you know what is the purpose of the field FLDNOTUSED on PSDBFIELD? I know it makes the words "Not Used" show up in the Key column in App Designer, but what is the actual function, and how is the field set? it is disabled at the field object level in App Designer.

Thanks

Top
  • reply
Thu, 02/05/2009 - 9:37am
#2
Lepa's picture
Lepa
User offline. Last seen 1 week 5 days ago. Offline
Joined: 06/23/2008
Posts: 457
Re: Get Edit Tables Behind a Record Fields

Per PeopleSoft:

Quote:
This field indicates whether a field is being actively used in a table or whether it is just there to avoid rebuilding the table and removing it. The default is 0, which means the field is used.

I can't find an example about such a field though.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Top
  • reply
Tue, 02/10/2009 - 12:33pm
#3
Lepa's picture
Lepa
User offline. Last seen 1 week 5 days ago. Offline
Joined: 06/23/2008
Posts: 457
Re: Get Edit Tables Behind a Record Fields

Here is more info on the "Not Used" field property! Fields marked as "Not Used" will have the following characteristics:
• Skipped as part of the unique index.
• Ignored in Query.
• Ignored in nVision
• Unavailable to users in search pages or list boxes if it is a search key or list box item.

Give back to the community and help it grow!
* Help with unanswered forum questions and issues
* Register or login to share your knowledge at your own blog

Top
  • reply
Tue, 02/10/2009 - 1:04pm
#4
Jason G's picture
Jason G (not verified)
Re: Get Edit Tables Behind a Record Fields

I wonder if it was a way for Psoft to deprecate a field yet avoid a massive ALTER during upgrades? It's on high-volume tables like VCHR_%. We have millions of rows in those tables and an alter would take forever, if it didn't completely bring the db to its knees.

Thanks for the info!

Top
  • reply

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.
9 + 10 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.