Maybe SAS is automatically correcting the type of "LEFT JON" and oracle is not?

On Mon, 24 Oct 2011 15:20:54 -0400, Ya Huang <ya.huang@AMYLIN.COM> wrote:

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