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

Many queries that worked in the past, which were syntactically challenged, fail with Oracle8. In theory, they never should have worked. Oracle is just applying the theory more strictly.

Pete Barnett
Oracle Database Administrator
Regence BlueCross BlueShield
pnbarne_at_regence.com

On Wed, 7 Mar 2001, Sam Bootsma wrote:

> 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: Peter Barnett 
  INET: pnbarne_at_bcbso.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).