PS Query & SQL

Does anyone have any suggestions on SQL tuning or getting around PS query messing with dates?   In the past few months, I have been dealing with queries taking longer to run than they used to take.  Many times, updating timestamp fields to date fields has fixed the issue, but I have to mess with changing the format of a field multiple times to do that.   Below is an example snippet of a very ineloquent update.  I am all ears on any suggestions when dealing with these issues, thanks!

Expression in PS query:

CASE
WHEN  TO_DATE(B.POSTED_DATE,'YYYY-MM-DD') BETWEEN TO_DATE(J.SRVC_IND_DTTM,'YYYY-MM-DD') AND  TO_DATE(J.AUDIT_STAMP,'YYYY-MM-DD') THEN...

The SQL tab on PS query:

CASE
WHEN  TO_DATE( TO_CHAR(B.POSTED_DATE,'YYYY-MM-DD'),'YYYY-MM-DD') BETWEEN TO_DATE( TO_CHAR(CAST((J.SRVC_IND_DTTM) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') AND  TO_DATE( TO_CHAR(CAST((J.AUDIT_STAMP) AS TIMESTAMP),'YYYY-MM-DD-HH24.MI.SS.FF'),'YYYY-MM-DD') THEN

CS system details if helpful  

PI Version:   9.2.033    

Tools Version: 8.60.20

------------------------------
Ross Nolan
Data and Reporting Analysis
University of California, Berkeley
rvnolan@berkeley.edu
------------------------------

Hi! 

PS Query and Date/Timestamp fields are basically bad news.  What you're doing is what you have to do.  The one suggestion I have is if you are using the same field in a criteria and as a field, use different expressions for each. 

Vic

------------------------------
Vic Goldberg
University of Colorado
goldbergvictor@gmail.com
------------------------------

Hi Ross,

This could be something for DB Admins to look at with respect to tunig. On an easier way to write expressions, you could use Meta-SQLs. To turn Datetimes around to Dates use %Dateout.

You could find the Meta-SQL references here, https://docs.oracle.com/cd/G23954_01/pt862pbr1/eng/pt/tpcl/Meta-SQLReference-073bd4.html?pli=ul_d1863e63_tpcl

Where can you use them can be found here, https://docs.oracle.com/cd/G23954_01/pt862pbr1/eng/pt/tpcl/Meta-SQLPlacementConsiderations-073bd5.html?pli=ul_d1863e63_tpcl

New Expression can be,

CASE
WHEN  %dateout(B.POSTED_DATE) BETWEEN %dateout(J.SRVC_IND_DTTM) AND  %dateout(J.AUDIT_STAMP) THEN...

Hope these help

Regards,

TB

------------------------------
TUSHAR BABU
BA
The Australian National University
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

If you do not need the full timestamp could you use TRUNC  (although may just be an Oracle thing).

example:

select systimestamp, trunc(systimestamp) as just_date
from dual

------------------------------
Daron Wild
Senior PeopleSoft Developer
University of Cambridge
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Good morning Ross,

If you have access to it, here is a link to my Alliance24 (Phoenix) presentation on DateTime and Query:

https://www.heug.org/communities/community-home/librarydocuments/viewdocument?DocumentKey=f0e4a3a2-4891-4498-b5d4-9019b68a146a

Also, I agree with Vic. You're doing it right already. 

Find a date format that can be common to all of the fields you need.

Write each Date or Date/Time field as an expression, using SQL operators to manipulate the native format into the chosen common one.

Then, use those expressions in place of the native fields in your criteria, etc.

TRUNC may work in some circumstances. As will the operators I have listed in the table in my session. 

Another you may try is SUBSTRING. Sometimes that is an efficient way to select the part of a DateTime field you need.

------------------------------
Scott Frey
Senior Data Analyst - Human Resource Information Systems
University of Colorado
------------------------------

Message from the HEUG Marketplace:
------------------------------
Find, Review, and Engage with Higher Education-focused solution providers, products, and services using the HEUG Marketplace.
------------------------------

Thank you all for the helpful suggestions!  I will test the different options and see which ones work best for this scenario, as well as save some of these ideas, as I bet they will be helpful later as well.

------------------------------
Ross Nolan
Data and Reporting Analysis
University of California, Berkeley
rvnolan@berkeley.edu
------------------------------