Re: View with outer join

From: Krishna Kakatur <Krishna.Kakatur_at_Sun.COM>
Date: Wed, 04 Feb 2004 13:03:00 -0800
Message-id: <40215E04.9060004@Sun.COM>

Check the doc:

http://download-west.oracle.com/docs/cd/A87860_01/doc/server.817/a85397/expressi.htm#1002893

Oracle does not add additional rows for you if you use outerjoin on just one table.

-- 
Thanks,
Krishna

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
NOTICE:  This email message is for the sole use of the intended
recipient(s) and may contain confidential and privileged
information.  Any unauthorized review, use, disclosure or
distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply email and destroy
all copies of the original message.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~



jaysingh1_at_optonline.net wrote:


> Dear All,

> 

> I have problem with view which has outer join. Could someone in this list please through some light how to accomplish Step(1) output using Step(2) and Step(3)?

> 

> Isn't it  "Step(1) = Step(2)+Step(3)"?

> 

> Step(1) returns 24 records.

> Step(2)+Step(3) returns 0 records.

> 

> Please enlighten me if I am wrong? 

> 

> 

> STEP (1)

> ========

> SELECT   T1.USERSTATUSID,

>       NVL(T21.DESCRIPTION,T22.DESCRIPTION) USERSTATUSDESC,

>       NVL(T21.TOKENVALUE,T22.TOKENVALUE) USERSTATUSABBREV,

>          T1.HSBCSERVICEID,

>          T1.ACTIVEFLAG,

>          T21.LANGUAGEID LANGUAGEID1,

>      T22.LANGUAGEID LANGUAGEID2,

>          T1.UPDATEDATE,

>          T1.CREATEDATE

> FROM    USERSTATUS T1,

>         crmf.NLS_TOKEN_DETAILS T21,

>         crmf.NLS_TOKEN_DETAILS T22

> WHERE   T1.TOKENID=T21.TOKENID(+)

> AND     T1.TOKENID=T22.TOKENID

> AND     T22.LANGUAGEID=30

> AND     T21.languageid(+)=1;

> 

> 24 rows returned. <=========********

> 

> STEP (2)

> ========

> CREATE OR REPLACE VIEW userstatus_v2 AS

> SELECT   T1.USERSTATUSID,

>        NVL(T21.DESCRIPTION,T22.DESCRIPTION) USERSTATUSDESC,

>        NVL(T21.TOKENVALUE,T22.TOKENVALUE) USERSTATUSABBREV,

>                  T1.HSBCSERVICEID,

>                  T1.ACTIVEFLAG,

>                  T21.LANGUAGEID LANGUAGEID1,

>          T22.LANGUAGEID LANGUAGEID2,

>                  T1.UPDATEDATE,

>                  T1.CREATEDATE

> FROM     USERSTATUS T1,

>          crmf.NLS_TOKEN_DETAILS T21,

>          crmf.NLS_TOKEN_DETAILS T22

> WHERE    T1.TOKENID=T21.TOKENID(+)

> AND      T1.TOKENID=T22.TOKENID

> AND      T22.LANGUAGEID=30;

> 

> View created.

> 

> STEP (3)

> ========

> SELECT * FROM userstatus_v2

> WHERE languageid1(+)=1;

> no rows returned. <==========*******

> 

> 

> ----------------------------------------------------------------

> Please see the official ORACLE-L FAQ: http://www.orafaq.com

> ----------------------------------------------------------------

> To unsubscribe send email to:  oracle-l-request_at_freelists.org

> put 'unsubscribe' in the subject line.

> --

> Archives are at http://www.freelists.org/archives/oracle-l/

> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html

> -----------------------------------------------------------------


----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Wed Feb 04 2004 - 15:03:00 CST