%List meta-SQL element

Syntax

%List({FIELD_LIST
| FIELD_LIST_NOLONGS | KEY_FIELDS | ORDER_BY},recordname [correlation_id])

Description

The %List construct expands into a list of field names delimited by commas. The fields included in the expanded list depend on the parameters.

Note:

This meta-SQL is not implemented for COBOL, dynamic view SQL, or PeopleCode.

Parameters

Parameter	Description
FIELD_LIST	Use all field names in the given record. You can select only one option from FIELD_LIST, ORDER_BY, FIELD_LIST_NOLONGS, or KEY_FIELDS.
KEY_FIELDS	Use all key fields in the given record. You can select only one option from FIELD_LIST, FIELD_LIST_NOLONGS, KEY_FIELDS, or ORDER_BY.
ORDER_BY	Use all the key fields of recordname, adding the DESC field for descending key columns. This parameter is often used when the list being generated is for an Order By clause. You can select only one option from FIELD_LIST, KEY_FIELDS, ORDER_BY, or FIELD_LIST_NOLONGS.
FIELD_LIST_NOLONGS	Use all field names in the given record, except any long columns (long text or image fields.) You can select only one option from FIELD_LIST, ORDER_BY, KEY_FIELDS, or FIELD_LIST_NOLONGS.
recordname	Identify either a record or a subrecord that the field names are drawn from. This can be a bind variable, a record object, or a record name in the form recname. You cannot specify RECORD.recname, a record name in quotation marks, or a table name.
correlation_id	Identify the single-letter correlation ID to relate the record specified by recordname and its fields.

Example

The following is a good example of using %List. Both the Insert and Select statements use the same %List variable:

INSERT INTO PS_PO_DISTRIB_STG (  %Sql(POCOMMONDISTSTGFLDLSTU) 
,  %List(FIELD_LIST, CF16_AN_SBR) 
,  MERCHANDISE_AMT 
,  MERCH_AMT_BSE 
,  QTY_DEMAND 
,  QTY_PO 
,  QTY_PO_STD 
,  QTY_REQ)   
SELECT  %Sql(POCOMMONDISTSTGFLDLSTU) 
,  %List(FIELD_LIST, CF16_AN_SBR) 
,  MERCHANDISE_AMT 
,  MERCH_AMT_BSE 
,  QTY_DEMAND 
,  QTY_PO 
,  QTY_PO_STD 
,  QTY_REQ    
FROM PS_PO_DIST_STG_WRK WRK   
WHERE WRK.PROCESS_INSTANCE = %Bind(PROCESS_INSTANCE)

The following example shows a poor example of how to use %List. The Insert and Select field lists both use %List, but the Select field list is only partly dynamic; the rest is hard-coded.

INSERT INTO PS_EN_TRN_CMP_TMP (%List(FIELD_LIST, EN_TRN_CMP_TMP))  
SELECT B.EIP_CTL_ID 
, %List(SELECT_LIST, EN_BOM_COMPS A) 
, E.COPY_DIRECTION 
, E.BUSINESS_UNIT_TO 
, E.BOM_TRANSFER_STAT 
, 'N' 
, B.MASS_MAINT_CODE 
, 0 
 FROM PS_EN_BOM_COMPS A 
 , PS_EN_ASSY_TRN_TMP B 
 , PS_EN_TRNS_TMP E 
WHERE ...

The following example shows the previous poor example rewritten in a better way:

INSERT INTO PS_EN_TRN_CMP_TMP (EIP_CTL_ID, 
,  %List(FIELD_LIST, EN_BOM_COMPS) 
,  COPY_DIRECTION 
,  BUSINESS_UNIT_TO 
,  BOM_TRANSFER_STAT 
,  EN_MMC_UPDATE_FLG 
,  MASS_MAINT_CODE 
,  EN_MMC_SEQ_FLG01 
,  ... 
, EN_MMC_SEQ_FLG20)  
SELECT B.EIP_CTL_ID 
  , %List(FIELD_LIST, EN_BOM_COMPS A) 
, E.COPY_DIRECTION 
, E.BUSINESS_UNIT_TO 
, E.BOM_TRANSFER_STAT 
, 'N' 
, B.MASS_MAINT_CODE 
, 0 
, ... 
, 0 
 FROM PS_EN_BOM_COMPS A 
 , PS_EN_ASSY_TRN_TMP B 
 , PS_EN_TRNS_TMP E 
WHERE ...

The following code segment is another poor example. Only the field list of the Insert statement is dynamically generated, and the Select statement is statically coded. If the table STL_NET_TBL is reordered, the Insert statement will be incorrect.

INSERT INTO PS_STL_NET_TBL (%List(FIELD_LIST, STL_NET_TBL ) )  
SELECT :1  
, :2  
, :3  
, :4  
, :5  
, :6  
, :7  
,:8  
FROM PS_INSTALLATION

The following code shows the previous poor example rewritten in a better way:

INSERT INTO PS_STL_NET_TBL (%List(FIELD_LIST, STL_NET_TBL))

VALUES (%List(BIND_LIST, STL_NET_TBL MY_AET))

Considerations for Using %List

When using %List in an Insert/Select or Insert/Values or %Select statement, you must have matching pairs of %List (or %ListBind) variables in the target and source field lists. Use the same list type argument and record name to ensure consistency.