BookmarkSubscribeRSS Feed
OscarBoots2
Calcite | Level 5

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.

 

 

29 REPLIES 29
SASKiwi
PROC Star

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
Calcite | Level 5

Thanks SA SKiwi,

 

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

OscarBoots2
Calcite | Level 5

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
Calcite | Level 5

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
PROC Star

See my modified query above.

Tom
Super User Tom
Super User

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
Calcite | Level 5

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
PROC Star

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
Calcite | Level 5
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
Calcite | Level 5

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

 

ERROR: ORACLE execute error: ORA-01031: insufficient privileges.
OscarBoots2
Calcite | Level 5

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
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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?

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 29 replies
  • 1696 views
  • 2 likes
  • 3 in conversation