trouble with joins

From: BillRstl <magic.bill_at_cadvision.com>
Date: 7 Jun 2001 12:18:48 -0700
Message-ID: <f5a52c29.0106071118.78b082dc@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 Received on Thu Jun 07 2001 - 14:18:48 CDT