topic Re: Reuse a table created using a Pass Through query in SAS Enterprise Guide

Reuse a table created using a Pass Through query

OscarBoots2 — Sat, 10 Mar 2018 09:13:56 GMT

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.

Re: Reuse a table created using a Pass Through query

SASKiwi — Sat, 10 Mar 2018 10:02:16 GMT

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;

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Sat, 10 Mar 2018 09:57:04 GMT

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

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Sat, 10 Mar 2018 09:59:00 GMT

Thanks SA SKiwi,

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

Re: Reuse a table created using a Pass Through query

SASKiwi — Sat, 10 Mar 2018 10:42:07 GMT

See my modified query above.

Re: Reuse a table created using a Pass Through query

Tom — Sat, 10 Mar 2018 14:41:57 GMT

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;

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Sat, 10 Mar 2018 23:12:56 GMT

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

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Sat, 10 Mar 2018 23:14:46 GMT

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

Re: Reuse a table created using a Pass Through query

SASKiwi — Sun, 11 Mar 2018 01:55:41 GMT

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.

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Sun, 11 Mar 2018 02:00:44 GMT

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;

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Sun, 11 Mar 2018 02:03:31 GMT

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.

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Sun, 11 Mar 2018 02:33:18 GMT

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

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

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Sun, 11 Mar 2018 02:58:18 GMT

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 ;

Re: Reuse a table created using a Pass Through query

Tom — Sun, 11 Mar 2018 03:08:34 GMT

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
;

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Sun, 11 Mar 2018 03:17:50 GMT

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?

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Sun, 11 Mar 2018 05:44:46 GMT

Here's my latest & i'm getting an 'expecting a name error';

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

execute(create table IDS as 

select DISTINCT 
ID

FROM table_1

WHERE ROWNUM < 50

by ora);

create table MORE_IDS as select * from connection to ora
(
select DISTINCT 
id

from IDS A
INNER JOIN TABLE_2 B
ON A.ID = B.ID

);
disconnect from ora;
quit;

And here's the log script;

                                                         The SAS System                               14:24 Sunday, March 11, 2018

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _SASPROGRAMFILE=;
8          
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=ACTIVEX;
11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14             STYLE=HtmlBlue
15             STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SASHome/x86/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         


25         proc sql noprint;
26         connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
27         (HOST = xxxxxxxx.com)(PORT = 1234))
28         (CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))"
29         user=xxxxxxxxx pw=XXXXXXXXXX);
30         
31         execute(create table IDS as
32         
33         select DISTINCT
34         ID
35         
36         FROM table_1
37         
38         WHERE ROWNUM < 50
39         
40         by ora);
                  _
                  22
ERROR 22-322: Expecting a name.

Re: Reuse a table created using a Pass Through query

Tom — Sun, 11 Mar 2018 14:58:13 GMT

For SAS to understand what you are trying to do you need to follow the SYNTAX PATTERN that the commands expect.

The pattern for an EXECUTE statement.is looking for

execute (SQL STATEMENT) by DBNAME ;

Your command:

execute

(create table IDS as select DISTINCT ID FROM table_1 WHERE ROWNUM < 50 by ora

);

Notice how your command has deviated from the pattern by putting the BY ORA inside the parentheses. This causes two errors. SAS is not seeing the expected BY XXXX after the remote sql command. And if it did succeed in getting passed to the remote database then that wouldn't understand the BY ORA at the end. It would be looking for proper SQL syntax there, so perhaps a "group by" or an "order by", but not just a bare "by".

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Mon, 12 Mar 2018 01:20:30 GMT

Thanks Tom, that gives me the insufficient privileges error I was getting before.

I think I'll use the other syntax other than the execute version.

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Mon, 12 Mar 2018 01:53:25 GMT

OK, using this script I get the 'Table does not exist error.

How do I connect to the table I just created in ora?

proc sql;
connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxxx)))" 
user=xxxxxxxx pw=xxxxxx);
         
create table IDS as select * from connection to ora
(
select DISTINCT 
ID

FROM table_1

WHERE ROWNUM < 50

)
;QUIT;

PROC SQL;
connect to oracle as ora (path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP)
(HOST = xxxxxxxxxx.com)(PORT = 1234))
(CONNECT_DATA = (SERVICE_NAME=xxxxxxx)))" 
user=xxxxxxxx pw=xxxxxxxxx);
 
create table TABLE_2 as select * from connection to ora
(
select DISTINCT 
A.ID;
B.SALES

from ora.table1 A    ------- HOW DO I ACCESS THIS TABLE?
INNER JOIN dbc.table_3 B
ON A.ID = B.ID
);
         
Disconnect from ora ;
quit;

Re: Reuse a table created using a Pass Through query

OscarBoots2 — Mon, 12 Mar 2018 02:11:16 GMT

Sorry made an error in the ref to the first query, ora.table1 should be ora.IDS