joining 2 oracle tables in pass through

[TheNovice]

hi there,

I want to join 2 oracle tables using pass through but i keep mucking up the syntax... this is what i have . What I am doing wrong ?

PROC SQL;
 CONNECT TO ORACLE (USER=&NAME  PW=&PASS  PATH=EX);
 CREATE TABLE ICO AS
  SELECT A.*,B.* FROM CONNECTION TO ORACLE
  (SELECT BAN,INDICATOR,BL_IND
   FROM  ODS.BILLING_ACCOUNT
   WHERE STATUS IN ('O','S') AND ACCOUNT_TYPE||ACCOUNT_SUB_TYPE = 'IR')
    A
  LEFT JOIN
  (SELECT SUB_NO,SUB_STATUS,PRODUCT_TYPE
  FROM ODS.SUB B
  WHERE SUB_STATUS =  'A'  
   ON A.BAN = B.CUSTOMER_ID
 ;
 DISCONNECT FROM ORACLE;
QUIT;

[Patrick · Accepted solution]

Not tested but based on the code you've posted something like below could work.

PROC SQL;
  CONNECT TO ORACLE (USER=&NAME  PW=&PASS  PATH=EX);
  CREATE TABLE ICO AS
    SELECT *
    FROM CONNECTION TO ORACLE
    (
      SELECT 
        a.BAN,
        a.INDICATOR,
        a.BL_IND,
        b.SUB_NO,
        b.SUB_STATUS,
        b.PRODUCT_TYPE
      FROM  
        ODS.BILLING_ACCOUNT a
        left join
        ODS.SUB b
      ON 
        a.BAN = b.CUSTOMER_ID
      WHERE 
        a.STATUS IN ('O','S') 
        AND a.ACCOUNT_TYPE='I'
        AND a.ACCOUNT_SUB_TYPE = 'R'
        and b.SUB_STATUS =  'A'
    )
    ;
  DISCONNECT FROM ORACLE;
QUIT;

Try to avoid any sort of functions in join conditions/where clauses as then the database won't be able to use indexes if there are any.

AND ACCOUNT_TYPE||ACCOUNT_SUB_TYPE = 'IR'

I've also formulated the whole SQL without the inline views for the same reason - to give Oracle a better "chance" to use indexes and optimize the query. The Oracel SQL optimizer is pretty good so not sure if it will make a difference (if you've got Toad/SQL Developer then you could execute an EXPLAIN on the code to see what Oracle does with it).

[TheNovice]

Thanks  for the explanation! it worked!

[Tom]

On the SAS side there is no A and B alias. Just the single source of CONNECTION TO ORACLE.  So if need to use SELECT A.*, B.* that needs to part of query you are sending to Oracle.  If you have some other tool for connecting to Oracle, such as TOAD, you can test your pass thru code there first and then past it into the SAS code between the parentheses.

I like to place closing parenthesis under the opening parenthesis to make this easier to see (like placing the END under the DO in a block).

CREATE TABLE ICO AS
  SELECT * FROM CONNECTION TO ORACLE
  ( select a.*,b.* 
     from 
     (SELECT BAN,INDICATOR,BL_IND
      FROM  ODS.BILLING_ACCOUNT
      WHERE STATUS IN ('O','S') AND ACCOUNT_TYPE||ACCOUNT_SUB_TYPE = 'IR')
     ) A
    LEFT JOIN
     (SELECT SUB_NO,SUB_STATUS,PRODUCT_TYPE
      FROM ODS.SUB 
      WHERE SUB_STATUS =  'A'
     ) B 
   ON A.BAN = B.CUSTOMER_ID
  )
;

[TheNovice]

Thank you so much. This worked as well. I am usually just pulling variable using pass through and then joining. this is more efficient and seems faster.