Re: Strange behavior of subquery

On Feb 22, 9:45 am, Hans Mayr <mayr1..._at_gmx.de> wrote:
> Hello,
>
> I have a view V_BASE. Now I created a query with the following
> structure:
>
> WITH w1 AS ( SELECT ..... V_BASE ....)
> SELECT * FROM w1
>
> Everything works fine.
>
> Now I changed to
>
> WITH w1 AS ( SELECT ..... V_BASE ....),
>    w2 AS (SELECT ... w1 ...),
>    w3 AS (SELECT ... w1...)
> SELECT * FROM w1
> UNION ALL
> SELECT * FROM w2 WHERE 1=0
> UNION ALL
> SELECT * FROM w3 WHERE 1=0
>
> And suddenly I get a different result, one date column in w1 changed
> its values by exactly one month. The "WHERE 1=0" is just to turn off
> w2 and w3, I will it on again once that I fixed the error. Has anybody
> any idea how or why w2 and w3 could influence the result of w1? I work
> on Oracle 9. I checked the structure carefully usinghttp://www.sqlinform.com/
> to make sure that the problem is not due to a wrong bracket or
> something like that. Or could w1, w2 and w3 even influence the results
> of V_BASE?
>
> Thanks and Best,
>
> Hans

Not knowing which release of Oracle you're using ascertaining the nature of the problem and it's possible solution is guesswork, at best. Even replicating the 'problem' is questionable.

Post your Oracle release (to at least 4 numbers) if you really want assistance with this.

David Fitzjarrell Received on Fri Feb 22 2008 - 11:46:27 CST