Code Assistance Needed - Connection to Oracle

[JB1974]

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

( select

  abc.person_id

  ,abc.status

  ,abc.start_time

  ,abc.end_time

  ,tmp.last_ld_ts

  From

  [my table name] abc

  ,(select max(to_number(to_char(last_ld_ts, 'YYYYMMDD'))) as max_last_ld_ts from [my table name]) tmp

  where

    abc.person_id = 5897 and

    to_number(to_char(abc.last_ld_ts, 'YYYYMMDD')) = tmp.max_last_ld_ts

);

disconnect from oracle;

[TomKari]

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!

  Tom