Grid's Main Record

I dicovered an interesting thing about grids in 8.49. It might also apply to prior releases.

The Main record that is set on the General Tab of the Grid's property need not necesarily be the used in the grid. meaning the columns in the grid may be based on fields from another record which is the "true main record ".

What, then, is the use of the main record ? The only logical explanation seems to be that it helps in adding columns to the grid. When a new column is added,the record attribute for the column defaults to this record. One only needs to select the relevant field from this record.

Once you have add all your columns, you can potentially change the main record on General tabs later on grid without impacting the functionality of the grid. Peoplecode could get the grid object by referencing the page field name on the same Genral Tab. This will point to the grid object that you are interested especially if there are multiple scross. You could also reference the grid by passing the real main record.

%InsertSelect

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

I was trying to understand how %insertselect could be used. I have to admit I got stumped when I looked at the syntax and the examples in Peoplebooks . At least until, I saw it used in an app engine program and found.

If you are not clear on how is is used, I suggest you open up an app engine program which does use that syntax and follow the steps:

1. Right click the sql action and view sql.
2. right click on the sql text and select resolve metasql from the popup menu and behold you will see the resolved sytax.
3. Open the target and the source record , examine the table structures and review the sql in light of this information.

Generally, if the fields in the target table and the source table are the same and you are moving data from the source table to the target table and the key structure in the two tables is the same, the simple syntax can be used with no overide fields and no DISTINCT .

Example 1
=======

TgtTbl: col1 (key), col2(key) col3
SourceTbl: col1 (key), col2(key) col3

%INSERTSELECT( TgtTbl, SourceTbl)
FROM SourceTbl
WHERE col1 = 'A'

This will evaluate to

INSERT INTO tgtTble ( col1, col2,col3) <-- build from the recfields of the tgttable.
SELECT col1, col2,col3 <-- built from the sourcetbl rec fields that match the recfield on the target.
FROM SourceTbl WHERE col1 = 'A'

It is clear that the insert part is build on the basis of the fields in the target table. The select part is build on the basis of the fields in the source table .The field names in the target table should exist on the source table.

However, if you want to assign values to fields in the target table, that should not come from the source table or do not exist in the source table or maybe the field name that do not match, you would provide an override_field = value.

EXAMPLE 2 =========

%INSERTSELECT( TgtTbl, SourceTbl, COL2 = 5 )
FROM SourceTbl WHERE col1 = 'A' In this case, the insert select will be build such that 5 will be inserted in COL2 field for all the rows inserted in the target table. All other fields that exist in target table will get the values from the correspomnding fields in the source table.

This will be resolved to

INSERT INTO tgtTble ( col1, col2,col3)
SELECT col1, 5,col3 <-- built from the sourcetbl rec fields that match the recfield on the target in comination with the override value provided .
FROM SourceTbl WHERE col1 = 'A'

EXAMPLE 3
=========

%INSERTSELECT( TgtTbl, SourceTbl, COL2 = COL10 )
FROM SourceTbl WHERE col1 = 'A'

The fields in the TgtTbl are COL1, COL2 and COL3. Those in the target table are COL1,COL3,COL10.
It will resolve to the following:
INSERT INTO tgtTble ( col1, col2,col3)
SELECT col1, col10,col3
FROM SourceTbl
WHERE col1 = 'A'



When to use Distinct
==============

If the keys in the target table will not allow insert of all rows returned from the target table ( one resaon could be will cause unique constraint violation if rows in source table are not unique for the target table based on the target table's key ).

Consider the following structure:

tgtTble: col1 key, col2 key
Sourcetbl:col1 key, col2 key, col3 key

%INSERTSELECT( DISTINCT,TgtTbl, SourceTbl)
FROM SourceTbl

will resolve to

INSERT INTO TgtTbl(col1,col2)
SELECT DISTINCT col1,col2
FROM sourcetbl

Had the word DISTINCT not been used, multiple rows with the same key would have been attempted to be inserted causing a unique constraint.


Hope the above throws some light on how it works.

Welcome!

Welcome to my blog as I travel through the maze that is Peoplesoft in my journey to learn and share information .

I plan to post on the technical side of Peoplesoft as it relates to HCM.
I will appreciate any user participation. It will make the experience rewarding.