 | |
Strange behavior of subquery
comp.databases.oracle.misc comp.databases.oracle.misc
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Strange behavior of subquery -
02-22-2008
, 09:45 AM
#2
| |||
| |||
|
|
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 |
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
#3
| |||
| |||
|
|
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 |
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
#4
| |||
| |||
|
|
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 |
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
#5
| |||
| |||
|
|
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 |
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
#6
| |||
| |||
|
#7
| |||
| |||
|
#8
| |||
| |||
|
#9
| |||
| |||
|
#10
| |||
| |||
|
|
Hello, Thanks for your reply. Actually I was wrong, I do not work on Oracle 9. Here is the entry of v$version: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production I hope that you or someone else will be able to help me with this mystery. Best, Hans |
SQL> create or replace view v_base
2 as select * from user_objects;
View created.
SQL>
SQL> with w1 as (
2 select object_name, created, last_ddl_time, timestamp
3 from v_base
4 )
5 select * from w1;
OBJECT_NAME CREATED LAST_DDL_ TIMESTAMP
---------------- --------- --------- -------------------
CTRTEST 15-FEB-08 15-FEB-08 2008-02-15:14:29:38
HIYABABE 15-FEB-08 15-FEB-08 2008-02-15:14:43:10
TABLE2 20-FEB-08 20-FEB-08 2008-02-20:14:43:14
ABC 13-FEB-08 13-FEB-08 2008-02-13:08:19:23
ABC_IND 13-FEB-08 13-FEB-08 2008-02-13:08:20:18
ARTICLE 24-FEB-08 24-FEB-08 2008-02-24:16:12:31
EMP 12-FEB-08 12-FEB-08 2008-02-12:08:10:12
DEPT 12-FEB-08 12-FEB-08 2008-02-12:08:10:12
BONUS 12-FEB-08 12-FEB-08 2008-02-12:08:10:12
SALGRADE 12-FEB-08 12-FEB-08 2008-02-12:08:10:12
DUMMY 12-FEB-08 12-FEB-08 2008-02-12:08:10:13
OBJECT_NAME CREATED LAST_DDL_ TIMESTAMP
---------------- --------- --------- -------------------
LIGNECOMMANDE 24-FEB-08 24-FEB-08 2008-02-24:16:12:31
V_BASE 25-FEB-08 25-FEB-08 2008-02-25:07:29:20
T 19-FEB-08 19-FEB-08 2008-02-19:15:59:21
EXCEP_TAB 21-FEB-08 21-FEB-08 2008-02-21:09:27:56
TAB1 21-FEB-08 21-FEB-08 2008-02-21:09:27:56
TAB2 21-FEB-08 21-FEB-08 2008-02-21:09:27:56
17 rows selected.
SQL>
SQL> with w1 as (
2 select object_name, created, last_ddl_time, timestamp
3 from v_base
4 ),
5 w2 as (
6 select object_name, created, last_ddl_time, timestamp
7 from w1
8 ),
9 w3 as (
10 select object_name, created, last_ddl_time, timestamp
11 from w1
12 )
13 select * from w1
14 union all
15 select * from w2 where 0=1
16 union all
17 select * from w3 where 0=1;
OBJECT_NAME CREATED LAST_DDL_ TIMESTAMP
---------------- --------- --------- -------------------
CTRTEST 15-FEB-08 15-FEB-08 2008-02-15:14:29:38
HIYABABE 15-FEB-08 15-FEB-08 2008-02-15:14:43:10
TABLE2 20-FEB-08 20-FEB-08 2008-02-20:14:43:14
ABC 13-FEB-08 13-FEB-08 2008-02-13:08:19:23
ABC_IND 13-FEB-08 13-FEB-08 2008-02-13:08:20:18
ARTICLE 24-FEB-08 24-FEB-08 2008-02-24:16:12:31
EMP 12-FEB-08 12-FEB-08 2008-02-12:08:10:12
DEPT 12-FEB-08 12-FEB-08 2008-02-12:08:10:12
BONUS 12-FEB-08 12-FEB-08 2008-02-12:08:10:12
SALGRADE 12-FEB-08 12-FEB-08 2008-02-12:08:10:12
DUMMY 12-FEB-08 12-FEB-08 2008-02-12:08:10:13
OBJECT_NAME CREATED LAST_DDL_ TIMESTAMP
---------------- --------- --------- -------------------
LIGNECOMMANDE 24-FEB-08 24-FEB-08 2008-02-24:16:12:31
V_BASE 25-FEB-08 25-FEB-08 2008-02-25:07:29:20
T 19-FEB-08 19-FEB-08 2008-02-19:15:59:21
EXCEP_TAB 21-FEB-08 21-FEB-08 2008-02-21:09:27:56
TAB1 21-FEB-08 21-FEB-08 2008-02-21:09:27:56
TAB2 21-FEB-08 21-FEB-08 2008-02-21:09:27:56
17 rows selected.
SQL>
Please post the results you're seeing so we can understand this
problem.
David Fitzjarrell
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.