11-01-2012 01:38 PM
Hoping someone can help.
I am using SAS Enterprise Guide 4.3 - connecting into Oracle to pull back data. The information I am returning is personid Status, Start Time, End Time and Last Date Timestamp.
Here is the problem - for the Last Date Timestamp, I am trying to determine a way to only return the records for the most recent timestamp. Below is the code that is running, but it has been running for over 3 hours.
select * from connection to oracle
[my table name] abc
,(select max(to_number(to_char(last_ld_ts, 'YYYYMMDD'))) as max_last_ld_ts from [my table name]) tmp
abc.person_id = 5897 and
to_number(to_char(abc.last_ld_ts, 'YYYYMMDD')) = tmp.max_last_ld_ts
disconnect from oracle;
11-02-2012 02:44 PM
Should your sub-query have a WHERE clause for person_id? Otherwise, how do you know you're getting the latest date for that person, as opposed to just the general latest date?
However, that shouldn't make it run slow, it should just return no data. I don't see anything offhand that should cause long delays. Two general suggestions for troubleshooting:
1. Try running this query under your SQL client directly (is it still SQL*PLUS?), and see how long it takes. If it returns what you're expecting very quickly, then the problem is probably on the SAS side. Otherwise, it's probably on the Oracle side, and has nothing to do with SAS.
2. SAS has a SASTRACE option that can be used to display the actual code that SAS sends to Oracle. Although in this case I believe it will be exactly the code you've written, it could be worth a try.
Keep us posted!