Hey Sam,
Dont take it so seriously. Ur "Lead Developer" is just testing ur patience.
-Mandar
> -----Original Message-----
> From: Sam Bootsma [mailto:[EMAIL PROTECTED]]
> Sent: Wednesday, March 07, 2001 2:22 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Oracle Compiler Bug? (+) symbol in Decode Allowed?
>
>
> 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: Mandar Ghosalkar
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).
