Tools

Here is a great tool for formating SQL:

Instant SQL Formatter

PeopleTools Reference: Meta-SQL %SelectAll

Generates a select statement that selects all the fields from a chosen record.

Typically, when you use the %SelectAll, you would use a record object to store the results. That way you don’t have to know how many fields your result will return.

Examples

%SelectAll(JOB)

Local Record &myRecord;
&myRecord = CreateRecord(RECORD.MY_RECORD);
SQLExec(”%SelectAll(:1) WHERE MY_KEY = :2″, &myRecord, “My Key”, &myRecord);

Local Record &myInstallation;
&myRecord = CreateRecord(RECORD.INSTALLATION);
SQLExec(”%SelectAll(:1)”, &RECORD.INSTALLATION, &myInstallation);

PeopleTools Reference: Meta-SQL %Table

%Table

Replaces with the actual table name of the given record.

Generally, this just means adding a “PS_” to the front of the record name. But, PeopleTools actually checks the alternate table name from the Record Type tab to see if a value is there first.

Two Uses:

• Access a table with a reference or record object rather than embedding a table name in the SQL; PeopleTools will not index the table/record in a string literal.
• Reference the Temporary table in an App Engine; %Table is the only way to reference the table because the table name is assign dynamically at run time.

Examples:

SELECT * FROM %Table(JOB) WHERE EMPLID = :1

SqlExec(”SELECT NAME FROM %Table(:1) WHERE EMPLID = :2″, Record.NAMES, &emplid, &name);

&sql = CreateSql(”SELECT * FROM %Table(:1) WHERE EMPLID = :2″, &MyRecord, &emplid);

INSERT INTO %Table(MYTEMP_TAO)
SELECT * FROM PS_MYDATATABLE

PeopleTools Reference: Meta-SQL DateIn/DateOut

DateIn

Description

Converts a date into the form the database uses.
Used for:

• Where clauses
• Insert/Update

Remember DateIn passes a date into the database.

DateOut

Description

Converts a date into the form that PeopleTools uses.
Used for:

• Select clause

Examples

SELECT %DateOut(MAX(EFFDT))
FROM PS_JOB
WHERE EMPLID = :1
AND EMPL_RCD = :2
AND EFFDT

INSERT INTO PS_MY_RNCTL(OPRID, RUN_CONTROL_ID, AS_OF_DATE)
VALUES(:1, :2, %DateIn(:3))

UPDATE PS_MY_TABLE
SET MY_DATE = %DateIn(:1)
WHERE MY_DATE