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;
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).
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).
Thanks for the explanation! it worked!
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
)
;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.