topic Re: SAS/ORACLE - INNER JOIN - behaving different for single query in SAS Enterprise Guide

SAS/ORACLE - INNER JOIN - behaving different for single query

mahruf — Wed, 08 Aug 2018 17:19:52 GMT

TABLE A & C DATA LOCATED IN ORACLE

TABLE B DATA LOCATED IN SAS WORK SPACE

EXAMPLE 1

proc sql;

select * from Table a

inner join Table b

on a.ID = b.ID;

quit;

The above query is translated as the following in Oracle Session;

/*SELECT "COL1", "COL2" FROM Table a

WHERE ( ("ID" IN ( 1191 , 1192 , 1193 , 1194 , 1195 , 1196 , 1197 , 1198 , 1199 , 1200 ) ) )*/

EXAMPLE 2

proc sql;

select * from Table c

inner join Table b

on c.ID = b.ID;

quit;

The above query is translated as the following in Oracle Session;

/*SELECT "COL1", "COL2" FROM Table c

WHERE ((("ID"=:"ID") OR (("ID" IS NULL ) AND ( :"ID" IS NULL ))))*/

Example 2 has omitted the ID's and shows up differently to Example 1 'WHERE' clause despite the same query. It only happens when I used "Table C" but all the other table outputs as Example 1 'where' clause.

Does anyone know why that is happening? What could be the issues?

Thanks

Re: SAS/ORACLE - INNER JOIN - behaving different for single query

TomKari — Wed, 08 Aug 2018 21:17:31 GMT

I believe it's related to how SAS tries to optimize a join between tables in different data sources. This reference, and the linked description of the "DBMASTER" option, may give you some insights.

Tom

http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002205740.htm

Re: SAS/ORACLE - INNER JOIN - behaving different for single query

mahruf — Thu, 09 Aug 2018 16:26:31 GMT

I submitted the following query but still same issue.

libname dblib Oracle USER=abc PASSWORD="123" path=zzz multi_datasrc_opt=in_clause;

proc sql ;
create table work.x as

select * from

saswork.id tab1,

oracledb.bigdata (dbmaster=yes) tab2

where tab1.ID = tab2.ID;
quit;