SQL Usage in PeopleCode
Case
All table and column names should be in uppercase.
SELECT BUSINESS_UNIT, RA_CAMPAIGN_ID, RA_CAMPAIGN_NAME, DESCR, RA_OBJECTIVE_CD FROM PS_RA_CAMPAIGN;
Explicit Column Names
All SQL statements must explicitly list the field to be accessed. Therefore, the following SQL statements are not acceptable:
SELECT * from PS_RA_CAMPAIGN;
INSERT INTO PS_RA_CAMPAIGN VALUES (‘8001’,’01’,’923’,’202’,’F1’);
The appropriate formats for the above SQL commands are:
SELECT BUSINESS_UNIT, RA_CAMPAIGN_ID, RA_CAMPAIGN_NAME, DESCR, RA_OBJECTIVE_CD FROM PS_RA_CAMPAIGN;
INSERT INTO PS_RA_CAMPAIGN (BUSINESS_UNIT, RA_CAMPAIGN_ID, RA_CAMPAIGN_NAME, DESCR, RA_OBJECTIVE_CD) VALUES (‘PWCSL’, ‘01’, ‘MARKETING CAMPAIGN’, ‘THE FIRST MARKETING CAMPAIGN’, ‘001’);
Many PeopleSoft tables are designed to store historical data and therefore include an effective date (EFFDT) and an effective status (EFF_STATUS). Most access to the table will be required to select rows as a point in time. To achieve this code the appropriate effective date logic when writing SQL statements.
SELECT BUSINESS_UNIT FROM PS_BUS_UNIT_TBL WHERE EFFDT = (select max (A.EFFDT) FROM PS_BUS_UNIT_TBL A WHERE BUSINESS_UNIT = A.BUSINESS_UNIT AND A.EFFDT <= {Current Date} AND A.EFF_STATUS = ‘A’)
In certain cases, use SQLExec, as it only returns a single row. In other cases, you could benefit greatly by using a SQL object instead, especially if you can plan to execute a statement more than once with different bind parameters. The performance gain comes from compiling the statement once and executing it many times.
For instance, code that uses SQLExec might look like this:
While (some condition)
. . .set up &Rec
SQLExec(”%Insert(:1)”, &rec);
/* this does a separate tools parse of the sql and db compile of the statement and execute each time */
End-while;
The following code rewrites the previous example to use the new SQL object:
Local SQL &SQL = CreateSQL(”%Insert(:1)”);
While (some condition)
. . .Setup &Rec
&Sql.Execute(&Rec); /* saves the tools parse and db compile on the SQL statement and the db setup for the statement */
end-while;