Difference between revisions of "PeopleSoft Query Tips and Tricks"
| (6 intermediate revisions by one other user not shown) | |||
| Line 4: | Line 4: | ||
=== Oracle dates in the field list === | === Oracle dates in the field list === | ||
| − | + | PSQuery automatically converts any Oracle date field that you put in the field list to a character with formatting. This causes the following issues: | |
| − | The DB2 | + | 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(). |
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: | 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: | ||
| Line 20: | Line 20: | ||
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 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' | TO_CHAR([DateTm], 'HH24:MI:SS') > '17:00:00' | ||
| + | |||
| + | === DB2 YEAR() MONTH() and HOUR() equivalents === | ||
| + | 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. If the Expression is used in the field list (see above), then use SUBSTR() to get the desired portion of the field. For example: | ||
| + | SUBSTR(TO_CHAR([date],'YYYY-MM-DD'),1,4) to get the year <-- to_char is done for you by PeopleTools if the date is in the field list. | ||
| + | |||
| + | If your expression is not in the field list, you can use TO_CHAR() with the date part that you want, like: | ||
| + | TO_CHAR([date], 'MM') to get the month | ||
=== Long text fields with DISTINCT or GROUP BY === | === Long text fields with DISTINCT or GROUP BY === | ||
| Line 29: | Line 36: | ||
Make the Expression Type = Long Character | Make the Expression Type = Long Character | ||
| + | |||
| + | === Adding/Subtracting Years To/From A Date === | ||
| + | When you are trying to add or subtract years to or from a date in Oracle, use the expression | ||
| + | [date] +/- INTERVAL '[number]' YEAR | ||
| + | For example, | ||
| + | CURRENT_DATE - INTERVAL '19' YEAR | ||
| + | |||
| + | == PSQuery Error Messages == | ||
| + | === Unnamed file error === | ||
| + | If you are using the 2-tier query tool, you will see this error message: | ||
| + | |||
| + | [[Image:unnamed_file_error.gif]] | ||
| + | |||
| + | if your query description has a ':' in it. Don't ask us why... It does not appear to affect the web query tool. | ||
== PSQuery Limitations == | == PSQuery Limitations == | ||
Latest revision as of 19:56, 21 November 2008
Oracle Nuances
Oracle dates in the field list
PSQuery automatically converts any Oracle date field that you put in the field list to a character with formatting. This causes the following issues:
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().
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.
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 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'
DB2 YEAR() MONTH() and HOUR() equivalents
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. If the Expression is used in the field list (see above), then use SUBSTR() to get the desired portion of the field. For example:
SUBSTR(TO_CHAR([date],'YYYY-MM-DD'),1,4) to get the year <-- to_char is done for you by PeopleTools if the date is in the field list.
If your expression is not in the field list, you can use TO_CHAR() with the date part that you want, like:
TO_CHAR([date], 'MM') to get the month
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().
TO_CHAR(A.COMMENTS)
Make the Expression Type = Long Character
Adding/Subtracting Years To/From A Date
When you are trying to add or subtract years to or from a date in Oracle, use the expression
[date] +/- INTERVAL '[number]' YEAR
For example,
CURRENT_DATE - INTERVAL '19' YEAR
PSQuery Error Messages
Unnamed file error
If you are using the 2-tier query tool, you will see this error message:
if your query description has a ':' in it. Don't ask us why... It does not appear to affect the web query tool.
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 optimizer hint to a PSQuery. Obviously, this would be for advanced users only who have some idea of what the optimizer hints will do.
