If you copy/paste the query into the oracle editor directly does it work? Can you verify that &schema is resolving properly?

-Joe

On Mon, Oct 24, 2011 at 3:22 PM, Ya Huang <ya.huang@amylin.com> wrote: > Another person also pointed out the misspelling. Unfortunately, > after I changed it to join, it still gives the same error. > > On Mon, 24 Oct 2011 16:00:15 -0400, Tom Abernathy > <tom.abernathy@GMAIL.COM> wrote: > >>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 >>>>> >