The following SQL will automatically generate a formatted select statement with all the fields of the record you specify. Note that this only works in Oracle:
select 'select ' || substr(SYS_CONNECT_BY_PATH(chr(13) || chr(9) || FIELDNAME, ','), 2) || chr(13) || 'from ' || (case when substr(RECNAME, 1, 2) != 'PS' then 'PS_' || RECNAME else RECNAME end) || ';' as SELECT_STATEMENT from ( select RECNAME, FIELDNAME, FIELDNUM, count(*) OVER ( partition by RECNAME ) as FIELDCOUNT, ROW_NUMBER () OVER ( partition by RECNAME order by FIELDNUM) FIELDSEQUENCE from PSRECFIELD where RECNAME = '<RECNAME>') where FIELDCOUNT=FIELDSEQUENCE start with FIELDSEQUENCE = 1 connect by prior FIELDSEQUENCE+1=FIELDSEQUENCE and prior RECNAME=RECNAME order by FIELDNUM ;
Remember to replace <RECNAME> with the appropriate PeopleSoft record (e.g. PSUSEREMAIL). This script should correctly prefix your record name with PS_ where appropriate.
If you use the example of PSUSEREMAIL the output of the query will look like this:
select EMAILID, EMAILTYPE, OPRID, PRIMARY_EMAIL from PSUSEREMAIL;
Note that if you run this in a query tool such as SQLTools++ or TOAD, then you may only see the single word select in your query output - the rest of the text is there, it is just not visible because of the new line characters. Simply copy and paste the output to an editor window and it should display.
If you want to prefix your columns with an alias and give your record an alias use the following version and replace <RECNAME> and <ALIAS> with the appropriate values.
For example, PSUSEREMAIL as record and A as the alias.
select 'select ' || substr(SYS_CONNECT_BY_PATH(chr(13) || chr(9) || ALIAS || '.' || FIELDNAME, ','), 2) || chr(13) || 'from ' || (case when substr(RECNAME, 1, 2) != 'PS' then 'PS_' || RECNAME else RECNAME end) || ' ' || ALIAS || ';' as SELECT_STATEMENT from ( select RECNAME, '<ALIAS>' as ALIAS, FIELDNAME, FIELDNUM, count(*) OVER ( partition by RECNAME ) as FIELDCOUNT, ROW_NUMBER () OVER ( partition by RECNAME order by FIELDNUM) FIELDSEQUENCE from PSRECFIELD where RECNAME = '<RECNAME>') where FIELDCOUNT=FIELDSEQUENCE start with FIELDSEQUENCE = 1 connect by prior FIELDSEQUENCE+1=FIELDSEQUENCE and prior RECNAME=RECNAME order by FIELDNUM ;
