Use the results of a Pass Through query in another Pass Through query

[OscarBoots2]

Thanks Patrick,

Would you be able to apply this to my current script?

Thanks

[Patrick]

@OscarBoots2 Which script? The one you've posted initially....

select distinct
A.ID

from QUERY A
LEFT JOIN QUERY_NEXT B
ON A.ID = B.ID

...is actually rather "useless" and likely wasteful as it will return exactly the same result like...

select distinct a.id from query a

All the temp table stuff is not required for what I'm proposing.

Can I suggest you post a sample of your two tables in Oracle (just make up some SAS tables posting some SAS data step code which contains the key columns and a few additional variables, give us an idea of the actual rows per table, the relationship between the tables and describe the desired result.

[OscarBoots2]

Hi Patrick,

I've found that I don't have access to create temp tables but I have read that I can create a SAS Macro that will create the 60K recordset I want to use in a Pass Through.

Does a Macro work with your solution?

[Patrick]

---

@OscarBoots2 wrote:

Hi Patrick,

I've found that I don't have access to create temp tables but I have read that I can create a SAS Macro that will create the 60K recordset I want to use in a Pass Through.

Does a Macro work with your solution?

---

@OscarBoots2 That depends on what the macro does.

One possible approach when trying to retrieve a rather small subset of records from a big DB table based on a set of ID's in a SAS table: You get this data out of the DB with multiple queries and in each queries you've got simply a where clause with the IN operator and a list of ID values - and you generate this list of course dynamically.

I've seen SAS macros for this but actually I believe that's what SAS option DBMASTER does for you as well. You just need to formulate your SQL for this in the SAS SQL flavour (no explicit pass-through) and the SAS Access engine for Oracle should then generate the code for you and send it to the database.

If using implicit pass-through (SQL in SAS syntax) then also use below option as this will show you in the SAS log what SQL SAS actually sends to Oracle for execution.

options sastrace=',,,d' sastraceloc=saslog;

Now having said all of the above: If all the tables are already in Oracle then there shouldn't be any reason to first pull some subset into SAS. I'm not sure why you believe that it's a sub-query which creates performance issues for you. If formulated the right way and the sub-query resolves to a rather low number of records then there is actually a good chance that such data gets loaded into a hash (memory) in Oracle for further processing. 

When trying to find the bottleneck with Oracle queries then I'm normally using SQL Developer to get my Oracle syntax right (= no SAS involved). Once that works I then copy/paste the code into SAS (explicit pass-through). This allows me to separately tweak the Oracle query and the data transfer from Oracle to SAS and makes it much easier to determine where the bottleneck is.

[OscarBoots2]

Thanks Patrick,

The reason I believe that the subquery is not an option for me is purely due to many tests, include it & it takes ages & crashes, exclude it & it takes a short while & works.

I'm also using Oracle TOAD for testing & I've found that I don't have create table access in TOAD but I can create tables in EG. Otherwise If I had create table access in TOAD, I'd just use TOAD for the whole task.

The best idea I've heard so far is to use a temp table that the Pass Through will recognize, I will just need to get the code adapted to SAS EG.

I did have a look at your article on the link, thank you, it seems to find the largest table of a group of tables, but I must be missing something as to how it will it help here.

Appreciate your patience & assistance anyway.

Cheers Peter