subquery in a connect statement

[deleted_user]

I am trying to extract Oracle data but when I use a subquery as a list as follows -

proc sql;
&connect;
create TABLE /*RF300L1.*/D_FAB300_edc_collected1 as
select * from connection to ORACLE
(select * from edc_collected_data
where COL_INS_ID in
(select col_ins_id from Notfound_in_edc_collected));
disconnect from ORACLE;
quit;

but I recieve the following error-

ERROR: ORACLE prepare error: ORA-00942: table or view does not exist. SQL statement: select * from edc_collected_data where COL_INS_ID in (select col_ins_id from Notfound_in_edc_collected)

[art297]

Have you checked to ensure that those are the correct file names? I found it surprising that one ended with _data, while the other didn't.

Art

[advoss]

Are both tables, edc_collected_data and Notfound_in_edc_collected, Oracle tables, or is one of them a SAS table? In this query, they must both be Oracle tables.

[deleted_user]

Thanks advoss! I think that my answer the one in the subquery is a SAS table..

sorry checking my threads after a long long tme..

[Ksharp]

Hi.
It looks like you are using proc sql 's Pass-Through capability.
Do you try to use libname statement?


Ksharp

[hellind]

I tried to use the LIBNAME statement before trying the Pass-Through SQL. Thanks for Google and you. I removed the LIBNAME statement from the Pass_Through and it worked.