Date:         Mon, 24 Oct 2011 16:00:15 -0400
Reply-To:     Tom Abernathy <tom.abernathy@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Tom Abernathy <tom.abernathy@GMAIL.COM>
Subject:      Re: Multiple left join in sql pass through different from proc
              sql?

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