%InsertSelectWithLongs meta-SQL element

Syntax

%InsertSelectWithLongs([DISTINCT, ]insert_recname, select_recname [ correlation_id][, select_recname_n [ correlation_id_n]] [, override_field = value]. . .)

Description

The %InsertSelectWithLongs meta-SQL construct generates an Insert statement with a Select statement. It does not generate a From statement. You must specify the select records before you specify override fields.

Use %InsertSelectWithLongs instead of %InsertSelect when the fields in insert_recname include long values (LongChar and Image fields).

Note:

%InsertSelectWithLongs has a limit of 99 override fields.

The Insert column list is composed of all the fields in the specified insert_recname.

The corresponding value in the Select list is generated based on the following precedence:

  1. If the Insert fieldname appears as an override_field, the corresponding value is used in the Select list.

  2. If the Insert field name matches a field name in one of the select_recname variables specified, the corresponding Select field is used in the Select list.

  3. The search order of the select_recname records is the order that they are specified in the %InsertSelectWithLongs function.

  4. If the Insert field name has a constant default value defined in Application Designer, that value is used in the Select list.

  5. A default value appropriate for the data type of the Insert field is used (blank for characters, zero for numbers, NULL for Date, Time, and DateTime values, and so on.)

Use the optional override_field variable to specify values for a particular field.

Note:

You cannot use bind variables with the override_field.

For each field you specify, the matching logic described in the preceding list is not performed. Instead, the value that you specify after the equal sign is used for that field in the actual Select list. Use this technique to let PeopleTools or Application Engine handle most of the fields in the record, while specifying some of them explicitly. Also, you can use override_field to specify aggregate functions like Sum, Max, and so on.

Note:

This meta-SQL is not implemented for COBOL.

Parameters

Parameter Description

DISTINCT

Specify if the Select statement being generated should contain a Distinct clause.

insert_recname

Specify the name of record being inserted into. You must specify a record name, not RECORD. recname, a record name in quotation marks, a bind variable, or a table name.

Note: If the record for insert_recname is a temporary table, %InsertSelectWithLongs automatically substitutes the corresponding table instance (PS_TARGETnn instead of PS_TARGET).

select_recname

Specify the name of record being selected from. You can specify more than one record. You must specify a record name, not a RECORD. recname, a record name in quotation marks, or a table name.

correlation_id

Identify the correlation ID to be used for the select_recname records and fields.

override_field

Specify the name of a field on insert_recname that you want to supply a value for (instead of using the value supplied from the select_recname.)

Value

Specify the value that should be used for the override_field instead of the value from select_recname.

Example

Here is a basic example:

%InsertSelectWithLongs(AE_SECTION_TBL, AE_STEP_TBL S, AE_SECTION_TYPE = ' ')  
   FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T  
WHERE. . .

The example code resolves into the following:

INSERT INTO PS_AE_SECTION_TBL (AE_APPLID, AE_SECTION,. . ., AE_SECTION_TYPE) 
SELECT S.AE_APPL_ID, S.AE_SECTION, . . . ' ' 
FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T  
   WHERE. . .

In the following example, you have a temporary table, PS_MY_TEMP, which is based on a join between two other tables, PS_MY_TABLE1 and PS_MY_TABLE2:

%InsertSelectWithLongs(MY_TEMP, MY_TABLE1, MY_TABLE2 T2) 
   FROM PS_MY_TABLE1 T1, PS_MY_TABLE2 T2 
WHERE %Join(COMMON_KEYS, MY_TABLE1 T1, MY_TABLE2 T2) . . .

This code resolves into:

INSERT INTO PS_MY_TEMP (FIELD1, FIELD2 . . .) 
   SELECT T2.FIELD1, T2.FIELD2, . . . 
FROM PS_MY_TABLE1 T1, PS_MYTABLE2 T2 
WHERE T1.FIELD1 = T2.FIELD1  
AND T1.FIELD2 = T2.FIELD2 . . .

The following example creates a distinct Select statement.

%InsertSelectWithLongs(DISTINCT, MY_TABLE, TABLE1, TABLE2 T2) 
   FROM PS_TABLE1 T1, PS_TABLE2 T2 
WHERE %Join(COMMON_KEYS, TABLE1 T1, TABLE2 T2) . . .

This code resolves into:

INSERT INTO PS_MYTABLE (FIELD1, FIELD2 . . .) 
   SELECT DISTINCT T2.FIELD1, T2.FIELD2, . . . 
FROM PS_TABLE1 T1, PS_TABLE2 T2 
WHERE T1.FIELD1 = T2.FIELD1  
AND T1.FIELD2 = T2.FIELD2 . . .

Related Topics