Re: Can somebody explain this strange sql statement

From: onedbguru <>
Date: Mon, 18 Jul 2011 17:37:02 -0700 (PDT)
Message-ID: <>

On Jul 18, 6:42 pm, Charles Hooper <> wrote:
> On Jul 18, 5:31 pm, "Tony Johansson" <>
> wrote:
> > "Charles Hooper" <> skrev i meddelandetnews:8cc8133c-b171-444c-b0f3-
> > I think that at this point the best course of direction is to throw
> > away the SQL statement and start from scratch if either of the
> > following is true for the SQL statement:
> > * Performs poorly
> > * Does not produce the expected results
> > Hello
> > This sql statement has given the correct result earlier but according to the
> > customer will the result from this sql statement
> > give wrong result. They say that nothing has been changed the .NET code is
> > the same and the sql statement is the same
> > but they use a new Oracle version. When a match is supposed to occur for the
> > sql select statement we doesn't get any match ?
> > So do you think it might be possible that two different Oracle versions
> > could give different result for the same sql  select statement ?
> > //Tony
> > //Tony
> > Now to my question do you think that two different Oracle versions could
> > give two different results
> Simply changing the Oracle release version *should not* have an
> impact.  That said, there are release version specific bugs for ANSI
> style joins - the query might have worked by coincidence in the past,
> and when an ANSI bug was fixed, the join order changed.  See the
> following two articles, which list several of the ANSI specific bugs
> found in Metalink (MOS) and the release versions affected:
> The best bet at this point is probably to try to completely rewrite
> the SQL statement once it is understood what the SQL statement is
> supposed to accomplish.  I suggest avoiding the ANSI sytle joins if
> possible and also eliminating the scalar subquery.
> Another thought is that some form of new bug in the PL/SQL functions
> has surfaced.  For example, maybe one of those PL/SQL functions
> referenced in the SQL statement was performing a GROUP BY operation
> and relying on an implicit ORDER BY, and that implicit ORDER BY no
> longer happens in the new Oracle Database release version.
> Charles Hooper
> Co-author of "Expert Oracle Practices: Oracle Database Administration
> from the Oak Table"
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

Looks like this guy posted this in quite a few NG and forums... Could be wrong, but to me it looks like he is trying to reverse-engineer something (some web-based app??) without a clue as to what it needs to do... Received on Mon Jul 18 2011 - 19:37:02 CDT

Original text of this message