Auto Generate a Select Statement

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
     PSRECFIELDDB
   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.

Note the use of the record RECFIELDDB which includes all sub-record files so this will work on records that use sub-records such as EMPLOYEES. Thanks to Jonathan Kearney for this advice.

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
;
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License