topic Re: subquery in a connect statement in SAS Procedures

subquery in a connect statement

deleted_user — Fri, 07 Jan 2011 12:03:59 GMT

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)

Re: subquery in a connect statement

art297 — Fri, 07 Jan 2011 13:26:02 GMT

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

Re: subquery in a connect statement

advoss — Fri, 07 Jan 2011 16:25:52 GMT

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.

Re: subquery in a connect statement

Ksharp — Mon, 10 Jan 2011 01:18:51 GMT

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

Ksharp

Re: subquery in a connect statement

deleted_user — Wed, 30 Mar 2011 10:40:34 GMT

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..

Re: subquery in a connect statement

hellind — Thu, 03 May 2012 09:19:59 GMT

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.