Reuse a table created using a Pass Through query

[OscarBoots2]

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.  

[Tom]

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".

[OscarBoots2]

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.

[OscarBoots2]

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;

[OscarBoots2]

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

[OscarBoots2]

Can anyone advise the full libname statement using the my string below;

This how I've been connecting,

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

but I need to convert this into the below syntax;

libname mylib oracle .... connection=global ;

[OscarBoots2]

Hi Tom,

I think your answer of using a MyLib statement could work but I need to know how I can use my credentials to do this?

As you have suggested this solution, can you tell me how to change my connection details to a LibName statement OR is it not a possibility?

Thanks

[SASKiwi]

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

I think you are barking up the wrong tree. An "insufficient privileges error" means that your user account does not have the right permissions to create tables in the database you are querying. It doesn't matter whether you use the CONNECT statement or a LIBNAME statement you will get the same result. It is time to talk to your friendly Oracle database administrator to find out how temporary tables are managed at your site. He (or she) may even need to change the permissions on your user account for you to get this to work.

This SAS Note may also be helpful: http://support.sas.com/kb/13/182.html

[OscarBoots2]

Thanks but it's the 'execute ....' query that is giving me the 'insufficient privileges' error.

So what I want to know is how to write the connection string using my credentials with the Libname statement.

Can you help with this?

Thanks

[SASKiwi]

@Tom already gave you the answer earlier in this post. If you combine that with what you used in your other post:

libname mylib oracle user=XXXXXX pw=XXXXXX  
path="(DESCRIPTION=(ADDRESS = (PROTOCOL = TCP) 
	(HOST = XXXXXXXXXXX.com)(PORT = 1234))
	(CONNECT_DATA = (SERVICE_NAME=XXXXXX)))";


proc sql noprint;
connect using mylib ;
execute 
(create .....
) by mylib;
quit;

[OscarBoots2]

Thanks SASKiwi but the above script shows the word 'connect' in orange?

I can't find any syntax issues, maybe you can paste this into EG & see what's up?

Thanks

[Tom]

Don't depend on the color highlighting of an editor.

Run the code and read the log.

[SASKiwi]

EG colour-codes this incorrectly. It should still work.

[OscarBoots2]

Thanks Tom & SASKiwi,

I'm still stuck as I'm getting the same errors previously described.Thanks anyway for your time. 

[SASKiwi]

If you are referring to the "insufficient privileges" error while trying to create an Oracle temporary table, then you need to get advice from your Oracle administrator on the correct way to do this on your database and that may include getting your Oracle account permissions changed. It is not an error you can code around by connecting to the database a different way.