Desktop productivity for business analysts and programmers

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

Reply
New Contributor
Posts: 2

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

[ Edited ]

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

M

PROC Star
Posts: 1,332

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

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

New Contributor
Posts: 2

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

 

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;

Ask a Question
Discussion stats
  • 2 replies
  • 80 views
  • 0 likes
  • 2 in conversation