topic How to execute multiple statements in SQL pass through Netezza in SAS Programming

How to execute multiple statements in SQL pass through Netezza

jim_toby — Wed, 12 Dec 2018 16:46:00 GMT

I have an SQL pass through written in SAS that basically returns a limit of 100 records from a table stored in Netezza. I would like to also return 100 records from another table in Netezza without having to re-connect. Here's what I got:

proc sql; 
connect to odbc as odbcCon 
(noprompt='Driver={NetezzaSQL}; server=xxxxxx; port=xxxxx; database=xxx; username=xxxx; password=xxxxx;'); 
select * from connection to odbcCon 
(SELECT *
  FROM SCHEMA.table1
 LIMIT 100;); 
quit;

How can I select from table2 within the same connection? I tried doing this:

proc sql; 
connect to odbc as odbcCon 
(noprompt='Driver={NetezzaSQL}; server=xxxxxx; port=xxxxx; database=xxx; username=xxxx; password=xxxxx;'); 
select * from connection to odbcCon 
(SELECT *
  FROM SCHEMA.table1
 LIMIT 100;),
(SELECT *
  FROM SCHEMA.table2
 LIMIT 100;); 
quit;

But I get a syntax error. Any help would be great!

Re: How to execute multiple statements in SQL pass through Netezza

r_behata — Wed, 12 Dec 2018 17:09:49 GMT

First , I reckon your second query syntax would not work in Netezza let alone sas. If your tables have anything in common try joining them (inner, outer etc ) or use the union operator.

Re: How to execute multiple statements in SQL pass through Netezza

SASKiwi — Wed, 12 Dec 2018 20:24:32 GMT

proc sql; 
connect to odbc as odbcCon 
(noprompt='Driver={NetezzaSQL}; server=xxxxxx; port=xxxxx; database=xxx; username=xxxx; password=xxxxx;'); 
select * from connection to odbcCon 
(SELECT *
  FROM SCHEMA.table1
 LIMIT 100;); 
select * from connection to odbcCon 
(SELECT *
  FROM SCHEMA.table2
 LIMIT 100;); 
quit;