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
;
No Comments