Re: View with outer join
- From: Jonathan Gennick <jonathan@xxxxxxxxxxx>
- To: jaysingh1@xxxxxxxxxxxxx
- Date: Thu, 5 Feb 2004 07:50:45 -0500
Your view returns rows, right? Test that first, but I
believe if you issue a "SELECT * FROM userstatus_v2", you
should get plenty of rows back.
I think the problem with your query in step 3 is that you
are trying to include the "(+)" operator in a view-using
query, and you want that operator to act as if it were part
of the view-defining query:
SELECT * FROM userstatus_v2
WHERE languageid1(+)=1;
Conceptually, by the time you execute this query, the
outer-join has already taken place. Therefore, the
languageid1 column should be either 1 or NULL. You might try
rewriting your step 3 query as follows:
SELECT * FROM userstatus_v2
WHERE languageid1=1
OR languageid1 IS NULL;
Try this. See whether it helps. I wish I had some data to
test it on myself, but I don't.
Oh, by the way, if you're running on 9i I recommend using
the newer, outer-join syntax in your view-defining query.
Best regards,
Jonathan Gennick --- Brighten the corner where you are
http://Gennick.com * 906.387.1698 * mailto:jonathan@xxxxxxxxxxx
Join the Oracle-article list and receive one
article on Oracle technologies per month by
email. To join, visit http://four.pairlist.net/mailman/listinfo/oracle-article,
or send email to Oracle-article-request@xxxxxxxxxxx and
include the word "subscribe" in either the subject or body.
Wednesday, February 4, 2004, 3:25:35 PM, jaysingh1@xxxxxxxxxxxxx
(jaysingh1@xxxxxxxxxxxxx) wrote:
jon> Dear All,
jon> 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)?
jon> Isn't it "Step(1) = Step(2)+Step(3)"?
jon> Step(1) returns 24 records.
jon> Step(2)+Step(3) returns 0 records.
jon> Please enlighten me if I am wrong?
jon> STEP (1)
jon> ========
jon> SELECT T1.USERSTATUSID,
jon> NVL(T21.DESCRIPTION,T22.DESCRIPTION) USERSTATUSDESC,
jon> NVL(T21.TOKENVALUE,T22.TOKENVALUE) USERSTATUSABBREV,
jon> T1.HSBCSERVICEID,
jon> T1.ACTIVEFLAG,
jon> T21.LANGUAGEID LANGUAGEID1,
jon> T22.LANGUAGEID LANGUAGEID2,
jon> T1.UPDATEDATE,
jon> T1.CREATEDATE
jon> FROM USERSTATUS T1,
jon> crmf.NLS_TOKEN_DETAILS T21,
jon> crmf.NLS_TOKEN_DETAILS T22
jon> WHERE T1.TOKENID=T21.TOKENID(+)
jon> AND T1.TOKENID=T22.TOKENID
jon> AND T22.LANGUAGEID=30
jon> AND T21.languageid(+)=1;
jon> 24 rows returned. <=========********
jon> STEP (2)
jon> ========
jon> CREATE OR REPLACE VIEW userstatus_v2 AS
jon> SELECT T1.USERSTATUSID,
jon> NVL(T21.DESCRIPTION,T22.DESCRIPTION) USERSTATUSDESC,
jon> NVL(T21.TOKENVALUE,T22.TOKENVALUE) USERSTATUSABBREV,
jon> T1.HSBCSERVICEID,
jon> T1.ACTIVEFLAG,
jon> T21.LANGUAGEID LANGUAGEID1,
jon> T22.LANGUAGEID LANGUAGEID2,
jon> T1.UPDATEDATE,
jon> T1.CREATEDATE
jon> FROM USERSTATUS T1,
jon> crmf.NLS_TOKEN_DETAILS T21,
jon> crmf.NLS_TOKEN_DETAILS T22
jon> WHERE T1.TOKENID=T21.TOKENID(+)
jon> AND T1.TOKENID=T22.TOKENID
jon> AND T22.LANGUAGEID=30;
jon> View created.
jon> STEP (3)
jon> ========
jon> SELECT * FROM userstatus_v2
jon> WHERE languageid1(+)=1;
jon> no rows returned. <==========*******
jon> ----------------------------------------------------------------
jon> Please see the official ORACLE-L FAQ: http://www.orafaq.com
jon> ----------------------------------------------------------------
jon> To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
jon> put 'unsubscribe' in the subject line.
jon> --
jon> Archives are at //www.freelists.org/archives/oracle-l/
jon> FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
jon> -----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
