Reuse a table created using a Pass Through query

[OscarBoots2]

Hi Forum,

I have a Pass Through query that creates a table & I want use the results of that table in another query.

Here's an example of my code:

What I'm thinking is that I've created a table in a Schema I've named 'ora', so now I can use that as a prefix to get that table data back & use in the next query.

Can anyone explain how I can reuse a tables results that have been created this way?

Thanks

proc sql;
connect to oracle as ora (path=(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" 
user=xxxxxx pw=xxxxxxxx);
create table TABLE_3 as select * from connection to ora(
select distinct
A.ID
from TABLE_1 A
LEFT JOIN TABLE_2 B
ON A.ID = B.ID
)
Disconnect form ora
;QUIT;

proc sql;
connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" 
user=xxxxxx pw=xxxxxxxx);
create table TABLE_4 as select * from connection to ora
(
select distinct
A.ID,
B.FIELD_1,
B.FIELD_2,
B.FIELD_3
from ora.TABLE_3 A
LEFT JOIN TABLE_5 B
ON A.ID = B.ID
)
Disconnect form ora
;QUIT;

Now I want to use the results of the above query in another Pass Through.

[SASKiwi]

You are creating a SAS table when you should be creating an Oracle one. This should help - I've added a second step.

proc sql;
connect to oracle as ora (path=(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" 
user=xxxxxx pw=xxxxxxxx);
execute (create table TABLE_3 as 
select distinct
A.ID
from TABLE_1 A
LEFT JOIN TABLE_2 B
ON A.ID = B.ID
) by ora;

create table TABLE_4 as select * from connection to ora
(
select distinct
A.ID,
B.FIELD_1,
B.FIELD_2,
B.FIELD_3
from TABLE_3 A
LEFT JOIN TABLE_5 B
ON A.ID = B.ID
);

Disconnect from ora ;QUIT;

[OscarBoots2]

Thanks SA SKiwi,

How can I access that table in another Pass Through query?

[OscarBoots2]

Thanks SASKiwi,

I'm getting an 'Insufficient priveleges' error when running the first part which I think is due to the difference between my connection syntax & yours.

How can I adapt mine to suit the 'execute ... statement?

proc sql;
connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxxxxxxxxx.com)(PORT = 1234)
(CONNECT_DATA = (SERVICE_NAME=xxxxxxx)))" 
user=xxxxxxx pw=xxxxxxx);

Thanks

[OscarBoots2]

Another way to describe the issue is to ask "If I create a table with credentials as a "select * from connection to ora"

Can I retrieve the table by a "select from ora.tablename" statement?

Or how would I go about it?

The table data I've created must go somewhere, so how do I access it?

Thanks 

[SASKiwi]

See my modified query above.

[Tom]

What you need is a way to tell SAS to use the same Oracle session for both connections.

The way to do this is to use the connection=global option AND make another connection that persists between the two proc steps.

Use a LIBNAME statement to do that.

libname mylib oracle .... connection=global ;
proc sql noprint;
connect using mylib ;
execute 
(create .....
) by mylib;
quit;

proc sql ;
connect using mylib ;
select * from connection to mylib
(select ....)
;
quit;

[OscarBoots2]

Thanks Tom,

I'm getting an error with the initial connection statement, can you adapt your first libname line to mine?

proc sql;
connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxxxxxxxxx.com)(PORT = 1234)
(CONNECT_DATA = (SERVICE_NAME=xxxxxxx)))" 
user=xxxxxxx pw=xxxxxxx);

Thanks

[SASKiwi]

Please post your SAS log including your code, notes and errors. Helpful hint - if you are getting an error it is really useful if you tell us what it is and the code that generated it.

[OscarBoots2]

Hi SASKiwi, the error I'm getting is 'Invalid Username/Password' but I know this isn't the error. It only errors where I'm trying to set up yjis libname statement.

[OscarBoots2]

Here's the error msg from a basic select statement using your example;

ERROR: ORACLE execute error: ORA-01031: insufficient privileges.

[OscarBoots2]

I've had a look at some examples & this is what I've come up with, it still isn't right so I wonder if anyone can advise where I'm going wrong?

libname mylib oracle path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" ;
user=xxxxxxx pw=xxxxxxxx) connection=global;

[OscarBoots2]

Ok, here's where I'm at now;

The error is this - I can't see an error in the syntax or why this would error, any suggestions/ideas?

ERROR 22-322: Syntax error, expecting one of the following: ',', GROUP, HAVING, ORDER, WHERE.  

proc sql;
25         connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
26         (HOST = xxxxxxxxxxxxxxxxxx.com)(PORT = 1234)
27         (CONNECT_DATA = (SERVICE_NAME=xxxxxx)))"
28         user=xxxxxxx pw=XXXXXXXXXX);
29         
30         create table sales as select * from connection to ora
31                    (
32         select distinct
33         ID
34         
35         from Table_1
36         
37         WHERE rownum < 50
38         ) by ora;
                ___
                22
                76
ERROR 22-322: Syntax error, expecting one of the following: ',', GROUP, HAVING, ORDER, WHERE.  

ERROR 76-322: Syntax error, statement will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
39         
40         
41         create table TABLE_4 as select * from connection to ora
42         (
43         select DISTINCT
44         ID
45         from Table_1
46         );
2                                                          The SAS System                               09:40 Sunday, March 11, 2018

NOTE: Statement not executed due to NOEXEC option.
47         
48         Disconnect from ora ;

[Tom]

There are two ways to push code into the external database. Either via a query or just to run something. You have mixed the syntax of the two.

For a query (something that returns a table of results) you name the database first.

select .... from connection to DBNAME
(  .... SQL statement ... )
;

To run something (something that does NOT return any results) you name the database last.

execute
( .... SQL statement .... )
by DBNAME
;

[OscarBoots2]

OK, to break this into its parts, I want to get this working first up,

connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
26         (HOST = xxxxxxxxxxxxxxxxxx.com)(PORT = 1234)
27         (CONNECT_DATA = (SERVICE_NAME=xxxxxx)))"
28         user=xxxxxxx pw=XXXXXXXXXX);
29         
30         create table sales as select * from connection to ora
31                    (
32         select distinct
33         ID
34         
35         from Table_1
36         
37         WHERE rownum < 50
38         ) by ora;

So this should create a table that I can query later,

As shown before, there's an error of some syntax missing, can you help me get this part working first?