Usage of the %Table() meta-SQL
Background
When writing SQLExec statements, the common practice is to hard-code table/view names. For example:
SQLExec(”Select FIELD1, FIELD2 From PS_RECORD_TBL Where CRITER1 = :1 AND CRITER2 > :2″, &IN1, &IN2, &OUT1, &OUT2);
A problem with the above code is that information in the PeopleCode string literals cannot be classified by the compiler and saved into the PeopleTools reference tables.
Coding Standard
Use the meta-SQL %Table() in SQL statements to refer to SQL tables or views.
With a little effort, the above code can be transformed into the following (changes are highlighted):
SQLExec(”Select FIELD1, FIELD2 From %Table(:1) Where CRITER1 = :2 AND CRITER2 > :3“, Record.RECORD_TBL, &IN1, &IN2, &OUT1, &OUT2);
Benefits
The consequence of this change is that Record.RECORD_TBL is saved as a reference in the appropriate PeopleTools reference table (PSPCMNAME). This brings the following maintenance advantages:
A Find Definition Reference for RECORD_TBL record will include the above SQLExec line in the results.
Renaming RECORD_TBL will automatically propagate to the PeopleCode references (App Designer automation). No coding change is required.
For more complex SQL, like ones that need effective-dated logic, you can reference the record name only once. For example, if RECORD_TBL is effective-dated, you could write the following:
SQLExec(”Select FIELD1, FIELD2 From %Table(:1) A Where CRITER1 = :2 AND CRITER2 > :3 And EFFDT = (SELECT MAX(EFFDT) FROM %Table(:1) WHERE KEY1 = A.KEY1 AND EFFDT <= %CurrentDateIn)”, Record.RECORD_TBL, &IN1, &IN2, &OUT1, &OUT2);
At a glance, the number of tables/views being referenced by the SQLExec can be easily identified.