PeopleSoft Query Tips and Tricks
Oracle dates in the field list
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 in an Expression because of the FROM keyword. Use SUBSTR() instead to get the desired portion of the field.
CURRENT_DATE - TO_DATE([datetime], 'YYYY-MM-DD') <-- This will turn the character representation back into a date.
IMPORTANT NOTE: if you need to use the expression in both criteria and display it in the field list, you need to create two separate expressions, one that wraps the date with TO_DATE() for display and one that does not for use in your criteria.
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_CHAR([DateTm], 'HH24:MI:SS') > '17:00:00'
Long text fields with DISTINCT or GROUP BY
If you have a long text field (like a comments field) in your query results, and you use the DISTINCT option on the query or you are grouping by the long text field, you'll receive the following Oracle error.
ORA-00932: inconsistent datatypes: expected - got CLOB
To work around this - either remove the DISTINCT if applicable, or create an expression that converts the long character using TO_CHAR().
Make the Expression Type = Long Character
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 optimizer hint to a PSQuery. Obviously, this would be for advanced users only who have some idea of what the optimizer hints will do.