RE: Oracle Compiler Bug? (+) symbol in Decode Allowed?

From: Diana Duncan <dduncan_at_arsenaldigital.com>
Date: Wed, 07 Mar 2001 14:25:29 -0800
Message-ID: <F001.002C66B9.20010307141609@fatcity.com>

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: SamB_at_cpas.com

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: ListGuru_at_fatcity.com (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: dduncan_at_arsenaldigital.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------