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

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