topic Re: Problem with complex SQL in SAS SQL Pass through facility in SAS Programming

Problem with complex SQL in SAS SQL Pass through facility

chinyong — Thu, 02 Feb 2017 10:42:19 GMT

I had problem using SAS SQL Passthrough facilities for PCFILES with DSN (ODBC) connection to Oracle database.

My SAS environment only have "SAS/ACCESS for PC Files" and the "SAS PC File Server".

I'm manage to setup the oracle connection through SAS PC File Server --> DSN (ODBC) --> Oracle Database.

It only works if the SQL statement is simple or do not contain *. I had tested the same sql statements in oracle database and it ran just a few seconds.

Case 1: Simple Count = Works and SAS returned result in 1 seconds.

proc sql;
connect to PCFILES (SERVER=localhost DSN=dsnname USER=typeuserhere PWD=typepasswordhere);
create table temp as
select * from connection to PCFILES
(select count(*) cnt from my_table
where rownum<10);
disconnect from PCFILES;
quit;

Case 2: With * = Not working, run indefinitely

proc sql;
connect to PCFILES (SERVER=localhost DSN=dsnname USER=typeuserhere PWD=typepasswordhere);
create table temp as
select * from connection to PCFILES
(select * from my_table
where rownum<10);
disconnect from PCFILES;
quit;

Case 3: complex oracle sql - few table joins, oracle functions etc. = Not Working, run indefinitely

proc sql;
connect to PCFILES (SERVER=localhost DSN=dsnname USER=typeuserhere PWD=typepasswordhere);
create table temp as
select * from connection to PCFILES
(with t as (
select '12345' cli from dual union all
select '67890' cli from dual union all
select '98765' cli from dual)
select t.cli, max(pol.pol) keep (dense_rank last order by iss_dt) lst_pol,
ca.addr_1, ca.addr_2, ca.addr_3, ca.addr_4, ca.zip_code, ca.country_cd
from t, cpl, pol, ca
where t.cli=cpl.cli
and cpl.pol=pol.pol
and in_force(pol.status)='Y'
and cpl.cli=ca.cli
group by t.cli,ca.addr_1, ca.addr_2, ca.addr_3, ca.addr_4, ca.zip_code, ca.country_cd);
disconnect from PCFILES;
quit;

Font in blue color=Passthrough SQL and suppose to process by Oracle.

if i amend the SQL and make some syntax error on purpose, i can get error returned by Oracle. It just doesnt work if everything is correct (i tested the same SQL by connect to Oracle directly).

Example of syntax error returned by SQL Passthrough: ERROR: CLI describe error: [Oracle][ODBC][Ora]ORA-00904: "xxxxxx": invalid identifier

the SQL seem to correctly processed by Oracle but it just hang somewhere.

anyone facing this error or do you have any suggestion to solve this? I'm not sure if this problem occur in SAS/ACCESS for Oracle... if yes, it defeat the purpose for me to request for purchase on SAS/ACCESS for Oracle.

Re: Problem with complex SQL in SAS SQL Pass through facility

Peter_C — Wed, 01 Feb 2017 10:24:36 GMT

see what info is provided in the saslog with the sastrace option.. as in the usage note at http://support.sas.com/kb/15/056.html

Re: Problem with complex SQL in SAS SQL Pass through facility

chinyong — Wed, 01 Feb 2017 13:05:54 GMT

thanks for suggestion. will try to look at it once i got chance later.

hopefully able to find the root cause on the hanging issue.

it just doesn't make sense that a sql take seconds to process by oracle took forever in SAS SQL passthough facility.

but if i added any syntax error in any part of sql randomly, the error is returned as per it processed by oracle.

the problem even more strange where i can pass a simple Oracle specific sql (non ANSI SQL) successfully through SAS sql passthough facility.

Re: Problem with complex SQL in SAS SQL Pass through facility

LinusH — Wed, 01 Feb 2017 12:16:07 GMT

SASTRACE is of little use when it comes to explicit pass through. It's tailored for implicit pass through. Which should be the primary choice. Explicit should only be used if critical parts of the query isn't pass through automatically.

That said, everything with the parentheses is the concern of the underlying RDBMS, you need to start your research there, not in SAS.

Re: Problem with complex SQL in SAS SQL Pass through facility

Peter_C — Wed, 01 Feb 2017 12:36:03 GMT

thank you @LinusH for pointing out the confusion I caused by combining 2 ideas without clarification.

For examining any implicit pass-thru problem, I'm sure @LinusH would agree that the SASTRACE options are the best way to go.

For a quick sql solution that does not need the problems associated with implict pass-thru, the explicit way might be worth trying separately.

Re: Problem with complex SQL in SAS SQL Pass through facility

chinyong — Thu, 02 Feb 2017 10:44:16 GMT

Thanks @LinusH & @Peter_C!!!

I tried different odbc now and i think you are right. it seem like not related to SAS.

with Oracle ODBC Driver:

- Only simple sql works.

- does't support complex sql and select * (hanging indefinitely).

with Microsoft ODBC driver for Oracle:

- Most of the SQL works.

- except all SQL must start with "select" statement and cannot start with the Oracle's "With clause".

2nd solution works as long as I'm not using Oracle's "With Clause". I think i can survive without the "With Clause" as it can be replaced with subquery anyway (with a little overhead).

still curious why Oracle ODBC Driver doesn't work with SAS but Microsoft does.