Difference between revisions of "PeopleSoft Query Tips and Tricks"

From The University of Akron Support Wiki
Jump to: navigation, search
(Oracle dates in the field list)
Line 3: Line 3:
 
== Oracle Nuances ==
 
== Oracle Nuances ==
 
=== Oracle dates in the field list ===
 
=== 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.
  
 
== PSQuery Limitations ==
 
== PSQuery Limitations ==

Revision as of 15:09, 2 October 2008

PSV9 > Oracle Knowledge Base

Oracle Nuances

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.

PSQuery Limitations

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