%InsertSelect meta sql in Peoplesoft

%InsertSelect

Syntax

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

Description

The %InsertSelect function will generate an INSERT statement with a SELECT for you. It does not generate the FROM statement. You must specify all the select records before you specify any over ride fields.

The INSERT column list is composed of all the fields in the specified insert_recname, with the exception of LONG_CHAR or IMAGE fields.

The following code:

%InsertSelect(AE_SECTION_TBL, AE_STEP_TBL S, AE_SECTION_TYPE = ' ')

   FROM PS_AE_STEP_TBL S, PS_AS_STMT_TBL T

WHERE. . .

will resolve 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. . .

%InsertSelect(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 will resolve 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 . . .

-Posted by

Ganesh A.M

 

0 comments: