PeopleSoft Query Tips and Tricks
Oracle dates in the field list
The DB2 DATE() function corresponds to TRUNC() in Oracle, but TRUNC() will not work on a DateTm field once PeopleTools has (automatically) wrapped it in "TO_CHAR([datetime],'YYYY-MM-DD-HH24.MI.SS."000000"'). Instead, doing a SUBSTR([datetime], 1, 10) will return an actual date, which PeopleTools formats as 'MM/DD/YYYY', giving the same effective result as the DB2 DATE().
The DB2 functions YEAR(), HOUR(), etc. correspond to EXTRACT(YEAR FROM [date]) and EXTRACT(HOUR FROM [timestamp]) in Oracle, but PeopleTools does not allow these functions because of the FROM keyword. Use SUBSTR() to get the desired portion of the field.
Using an expression involving date math, such as addition or subtraction of days, becomes difficult if the expression is used in the field list. This is because PeopleTools will wrap any fields used in the expression in a TO_CHAR function. For example, the expression "CURRENT_DATE - [datetime]" will normally work, but if it is used in the field list, PeopleTools changes it to be "CURRENT_DATE - TO_CHAR([datetime], 'YYYY-MM-DD')". This expression won't work. To get around this problem, wrap the field in a TO_DATE function when you create the expression. For example, "CURRENT_DATE - TO_DATE([datetime], 'YYYY-MM-DD')" This will turn the character representation back into a date.
Oracle dates in the criteria
In v8, DateTm fields were wrapped in a DB2 DATE() function when used in criteria. In v9, TRUNC() is the equivalent function, but is not always necessary. Most comparisons (<, <=, etc) can be done on the field itself. If the criterion is = (equal to), you may not get any results unless you truncate the DateTm.
Oracle times in the criteria
To do time comparisons, use character representations of the 24-hour clock time. For example, to return all rows where a timestamp is after 5:00 PM, the criterion would be
TO_CHAR([DateTm], 'HH24:MI:SS') > '17:00:00'
Maximum of 26 records
PSQuery can have a maximum of 26 records in a query. This limitation is a result of how aliases are assigned to the recrods, namely via the alphabet [A-Z], and there is no handling for what happens after the "Zth" table is added. It does not assign an alias and the query breaks.
Other PSQuery Tips
Adding Oracle Hints to PSQueries
I haven't tried it yet, but here is a step-by-step on adding an Oracle hint to a PSQuery