Re: trouble with joins

"BillRstl" <magic.bill_at_cadvision.com> wrote in message news:f5a52c29.0106071118.78b082dc_at_posting.google.com...
> Hi There,
> I am transitioning from another database system to Oracle and am
> having trouble with ANSI standard joins in Oracle. I have placed a
> few examples below to illustrate what I am trying to do. Example 1 is
> the SQLPlus version of a join condition that works. Example 2 is the
> equivalent but using an ANSI style join... but when I try to run it,
> Oracle gives an error stating: 'SQL command not properly ended.' Can
> someone please let me know what I might be doing wrong? Finally
> Example 3 is the ANSI version of an outer join I would like to do...
> but cannot seem to get to work using SQLPlus... Could someone also be
> able to clue me in?
>
> (Yes I have researched this in a couple of places... our DBA's are
> located remote to me, and are not being too helpful.)
>
> -----Example 1
>
> select sf.FTR_EFFECTIVE_DATE,
> sf.FTR_EXPIRATION_DATE,
> s.SUBSCRIBER_ID,
> sf.OPERATOR_ID,
> sf.FEATURE_CODE,
> sf.SYS_CREATION_DATE
> FROM service_feature sf
> subscriber s
> WHERE s.SUBSCRIBER_NO = sf.SUBSCRIBER_NO
> AND s.CUSTOMER_ID = sf.CUSTOMER_ID
> AND s.PRODUCT_TYPE = sf.PRODUCT_TYPE;
>
>
> ----Example 2
>
> SELECT sf.FTR_EFFECTIVE_DATE,
> sf.FTR_EXPIRATION_DATE,
> s.SUBSCRIBER_ID,
> sf.OPERATOR_ID,
> sf.FEATURE_CODE,
> sf.SYS_CREATION_DATE
> FROM service_feature AS sf
> INNER JOIN subscriber AS s
> ON s.SUBSCRIBER_NO = sf.SUBSCRIBER_NO
> AND s.CUSTOMER_ID = sf.CUSTOMER_ID
> AND s.PRODUCT_TYPE = sf.PRODUCT_TYPE;
>
>
> ----Example 3
>
> SELECT sf.FTR_EFFECTIVE_DATE,
> sf.FTR_EXPIRATION_DATE,
> s.SUBSCRIBER_ID,
> sf.OPERATOR_ID,
> sf.FEATURE_CODE,
> sf.SYS_CREATION_DATE
> FROM service_feature AS sf
> LEFT OUTER JOIN subscriber AS s
> ON s.SUBSCRIBER_NO = sf.SUBSCRIBER_NO
> AND s.CUSTOMER_ID = sf.CUSTOMER_ID
> AND s.PRODUCT_TYPE = sf.PRODUCT_TYPE;
>
>
> Thanks In Advance

Prior to posting this to a newsgroup it is always advantageous to at least *try* to query the sql reference manual, and avoid posting questions that could be easily looked up.
That would have told you that the syntax in example 2 is *NOT* supported by Oracle (yes, and as ANSI has several levels, it is still ANSI compliant) It would also have told you that the (+) operator needs to be used for outer joins and the (+) needs to be located at the side of the table not returning rows.

Regards,

Sybrand Bakker, Oracle DBA Received on Thu Jun 07 2001 - 15:27:00 CDT