The concept of Set IDs can be confusing. However,
even if Set ID is not used in your installation (that
is, you have only one SETID value in a table), it is
still important to use it in SQL joins. I've discussed
this issue before—see
SQL
Performance in Subselects and
Use
All of the Keys.
One feature in the HRMS product makes using SETID joins
a little easier. The JOB table contains the appropriate
SETID values for joining to the department, job code,
location and salary plan tables. These fields are
SETID_DEPT, SETID_JOBCODE, SETID_LOCATION and SETID_SALARY.
The SQL
Performance post contains an example of how they are used.
(Curiously, PeopleSoft Query sometimes uses these but
at other times it goes through the table set sharing
tables or leaves the SETID joins out completely. I don't know
what the pattern is.)
However, there may be a problem with this. At two different installations
I've noticed that these fields are not always filled in.
This can happen if JOB rows are inserted by custom processes,
or if custom PeopleCode skips these fields, or maybe due
to other bugs. Since it's happened at more than one
installation, I'm starting to wonder.
You might want to take a quick look at your
JOB table. Select the rows where any of the SETID_x fields
are blank (as in = ' ', not NULL).
I still use these fields most
of the time (and as noted above, Query sometimes uses them too), but I've
started to trust them a little less. Check your own data.
