Tip 021: Comma Separated List of Fields in a Record

SQL | Creating a comma-separated list of all the fields in a record

<< Previous  | Next >>

A. SQL Insert Options

When running an SQL insert statement, you have two options available for performing the insert. You can carry out the insert based on an SQL select:

insert into table_name1
select field1, field2 ... field 10 from table_name2;

Or you can perform the insert by specifying the ‘value’ of each field:

insert into table_name1
values (field1, field2, field3 ... field10);

In both these examples, the SQL statement has not specified which fields are being used as the target for the insert. As a result, the SQL parser assumes that the fields in the ‘select’ or ‘values’ clause precisely match all the fields in the database table, in the correct order. For tables with a large number of fields, there is a high possibility of error here.

 

B. Field Lists in SQL

As an alternative, if you want to be more selective in what gets inserted, you can use an ‘insert into… select’ construct with the field names specified:

insert into table_name1 (field1, field2, field3 ... field10)
select field1, field2, field3 ... field 10 from table_name2;

Here’s the equivalent statement for an ‘insert… values’ statement:

insert into table_name1 (field1, field2, field3 ... field10)
values (field1, field2, field3 ... field10);

While the full list of field names helps to clarify the statement and cut down on ‘silly mistakes’, it can be quite a tedious and error-prone activity to create the list, especially if you are dealing with a large number of fields (some PeopleSoft tables have over 100 fields). The following SQL provides a quick way to generate a comma-separated list of all fields in a table. The results of this SQL can then be directly pasted into an insert statement:

select column_name || ',' as column_name
from all_tab_columns
where table_name = 'PS_COMMUNICATION'
and owner = 'PSCSM'
order by column_id;

Remember to replace both the Table Name and Owner fields with the specific case.

Here’s a slightly amended version if you want to prefix each field with a table alias (useful for a select list):

select 'A.' || column_name || ',' as column_name
from all_tab_columns
where table_name = 'PS_COMMUNICATION'
and owner = 'PSCSM'
order by column_id;

 

C. Field Lists in PeopleTools

If the SQL is being created in the PeopleTools environment, you have two other options at your disposal for specifying a full list of field names. Firstly, if you’re in an App Engine program, you can use the ‘%List’ meta-SQL construct:

INSERT INTO PS_RECORD_NAME (%List(FIELD_LIST, RECORD_NAME))
SELECT %List(FIELD_LIST, RECORD_NAME) FROM PS_RECORD_NAME2

At run-time, the ‘%List’ meta-SQL will be replaced with a list of fields for the record. The first parameter of the ‘%List’ construct can be set to ‘FIELD_LIST’ (all fields), ‘KEY_FIELDS’ (key fields only), ‘ORDER_BY’ (key fields in sorted order, for use in an SQL ‘order by’ clause), and ‘FIELD_LIST_NOLONGS (all fields except long characters and images).

The second option is the ‘%SelectAll’ meta-SQL construct. This can be used in any SQL object accessed within PeopleCode. ‘%SelectAll’ works in a similar way to %List, except that only the one parameter is needed – a record object. This parameter specifies the table that should be used for the SQL select. So if a record name of ‘my_rec’ gets passed into ‘%SelectAll’, the end result is effectively a shortcut for ‘select * from ps_my_rec’.

Here’s an example of using ‘%SelectAll’ in an SQL statement being created dynamically:

Local Record &studentRec = CreateRecord(Record.RECORD_NAME);
Local string &sql_select;
Local SQL &RunCntrlSQL;

&sql_select = "%selectall(:1) where oprid = :2 and run_cntl_id = :3";
&RunCntrlSQL = CreateSQL(&sql_select, &studentRec, &operatorID, &runCntlID);

Keep in mind that both ‘%List’ and ‘%SelectAll’ are proprietary to PeopleSoft. You will receive an error if you tried to run the statements directly in an external SQL editor. In addition, the ‘Resolve Meta SQL’ option in App Designer will not help you either. Normally when you have an SQL object open in App Designer, you can right-click anywhere on the text and select ‘Resolve Meta SQL’. This will present you with an expanded version of the SQL, with all meta-SQL replaced with standard SQL. Unfortunately, the ‘%List’ and ‘%SelectAll’ constructs are not part of the ‘Resolve Meta SQL’ functionality.

 

See Also:

<< Previous  | Next >>