I've certainly never seen the (+) symbol anywhere but in the where clause.
Did you check the results to see if they were the same or different?
Anyway, you shouldn't need it, and I believe you are correct. ;-)
Diana
-----Original Message-----
Sent: Wednesday, March 07, 2001 3:22 PM
To: Multiple recipients of list ORACLE-L
I am running Oracle 8.1.6 on Windows NT. The query below to create a view
compiles on Oracle 8.1.6.1.0, but not on 8.1.6.3.0. If I remove the (+)
from within the decode statement, it compiles in 8.1.6.3.0. Our lead
developer insists it is valid and he also insists the query gives different
results when the (+) sign in the decode is removed. I have not confirmed
this.
According to my understanding of outer joins (and from what I have seen in
the documentation) the (+) in the decode is invalid syntax. Can somebody
confirm this for me?
Here is the view in question:
CREATE OR REPLACE VIEW RULE$TYPE
(ruletype, caption, genlevel, defaultval, exposetype, switch, override)
AS
SELECT A.*,DECODE(B.RULETYPE(+),NULL,'G','M') FROM RULETYPE A,
RULETYPE_OVERRIDE B
WHERE B.RULETYPE(+)=A.RULETYPE
And here is an excerpt from the documentation:
Outer Joins
An outer join extends the result of a simple join. An outer join returns all
rows that satisfy the join condition and those rows from one table for which
no rows from the other satisfy the join condition. Such rows are not
returned by a simple join. To write a query that performs an outer join of
tables A and B and returns all rows from A, apply the outer join operator
(+) to all columns of B in the join condition. For all rows in A that have
no matching rows in B, Oracle returns NULL for any select list expressions
containing columns of B. See the syntax for an outer join in "SELECT and
Subqueries" <ch4l2.htm> <ch4l2.htm>.
Outer join queries are subject to the following rules and restrictions:
* The (+) operator can appear only in the WHERE clause or, in the
context of left-correlation (that is, when specifying the TABLE clause) in
the FROM clause, and can be applied only to a column of a table or view.
Thanks for any input...
Sam Bootsma
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sam Bootsma
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Diana Duncan
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
