|
Joe,
The actual code:
proc sql;
connect to oracle(path=&URL user=&UID pass=&PSS);
create table sample.ae (compress='Yes') as
select *
from connection to oracle
(select
a.CASE_ID,
a.SEQ_NUM,
b.det_causality
from &schema..lss_esd_rpt_event as a
left join
&schema..lss_esd_rpt_event_assess as b
on a.case_id = b.case_id and
a.seq_num = b.event_seq_num and
b.prod_seq_num = b.suspect_product and
b.det_causality_id !=-9999 and
b.primary_event = b.event_seq_num
left jon
&schema..lss_esd_rpt_product as c
on a.case_id = c.case_id and
c.first_sus_prod = 1 and
c.drug_type = 1 and
c.seq_num = b.prod_seq_num
)
;
** proc sql is ok for this type of chained left join;
libname schema "s:\xx\yyy";
proc sql;
select
a.CASE_ID,
a.SEQ_NUM,
b.det_causality
from schema.lss_esd_rpt_event as a
left join
schema.lss_esd_rpt_event_assess as b
on a.case_id = b.case_id and
a.seq_num = b.event_seq_num and
b.prod_seq_num = b.suspect_product and
b.det_causality_id ^=-9999 and
b.primary_event = b.event_seq_num
left jon
schema.lss_esd_rpt_product as c
on a.case_id = c.case_id and
c.first_sus_prod = 1 and
c.drug_type = 1 and
c.seq_num = b.prod_seq_num
;
The only difference between the pass through part and the proc sql part
are 1. the &schema in pass through is real Oracle schema, schema is
in the proc sql part is a libname. 2. ^=-9999 changed to ^=-9999 in proc
sql part.
Something obvious I missed?
On Mon, 24 Oct 2011 13:53:27 -0500, Joe Matise <snoopy369@GMAIL.COM> wrote:
>Hmm, that query executes fine when I run it directly into oracle:
>
>select *
> from (select 1 as id from dual) a
> left join
> (select 1 as id, 1 as x, 1 as y from dual) b
> on a.id=b.id and b.x=b.y
> left join
> (select 1 as id from dual) c
> on a.id=c.id
>
>Thus it should work in passthrough. Are you actually running that
>exact query, or are you running something else that might have an
>issue with oracle syntax or something?
>
>-Joe
>
>On Mon, Oct 24, 2011 at 12:50 PM, Ya Huang <ya.huang@amylin.com> wrote:
>> Hi there,
>>
>> The following code works fine in proc sql:
>>
>> proc sql;
>> select *
>> from a
>> left join
>> b
>> on a.id=b.id and b.x=b.y
>> left join
>> c
>> on a.id=c.id
>> ;
>>
>> But when it is for sql pass through, it gives the error message
>> "error: ORA-00933: SQL command not properly ended. SQL statement":
>>
>> proc sql;
>> connect to oracle(path=&URL user=&UID pass=&PSS);
>> create table x as
>> select *
>> from connection to oracle
>> (select *
>> from a
>> left join
>> b
>> on a.id=b.id and b.x=b.y
>> left join
>> c
>> on a.id=c.id
>> )
>> ;
>>
>> Can someone explain why?
>>
>> Thanks
>>
>> Ya
>>
| 
