RE: Query returning wrong results.

  • From: Jared.Still@xxxxxxxxxxx
  • To: oracle-l@xxxxxxxxxxxxx
  • Date: Fri, 19 Mar 2004 10:05:55 -0800

David,

Try running the test from my previous post and check the results.

You should see one row returned.

Jared






david wendelken <davewendelken@xxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
 03/18/2004 08:38 PM
 Please respond to oracle-l

 
        To:     oracle-l@xxxxxxxxxxxxx
        cc: 
        Subject:        RE: Query returning wrong results.


It's been a rough two weeks, so I could certainly be wrong on this one.
 
Let's simplify the query to make the problem more visible.
This is from the inline query.
 
select a.col1
from   table1 a
where a.col1   = '10000151'
union all
select a1.col1
from   table1 a1
where  a1.col1   = '10000151'
and    some_other_condition = 'true'
 
So, let's say that there are 100 records in table1.
2 of them have a col1 value of '10000151'.
 
So, part one above would bring back 2 rows.
Part two above would bring back 0, 1, or 2 rows, depending upon whether 
the "some_other_condition" returned true or not.
Agreed?
 
That would mean that the inline query duplicates records, in that it is 
possible for a given row to show up in both parts.
 
Here's a simple way to tell if I'm right.  (My hard disk crashed at home 
earlier this week and I haven't
re-installed oracle yet, or I would test it myself.)
 
Put the primary key columns of the record, plus a data source column into 
the queries, something like this:
select data_source, pkcol, ...
from (select 'part one' data_source, pkcol, ...
         union all
          select 'part two', pkcol,...
         )
 
Hope that explains what I was getting at! :)

-----Original Message----- 
From: Jared.Still@xxxxxxxxxxx 
Sent: Mar 18, 2004 7:58 PM 
To: oracle-l@xxxxxxxxxxxxx 
Subject: RE: Query returning wrong results. 


> The first part of the subquery would bring back each and every row with 
a col1   = '10000151'


> The second part of the subquery would bring back some of those same rows 
again, but with different date values, thus duplicating some rows in the 
result set.

Not  sure I'm following. 

Using the erroneous test results supplied by the poster, a test query 
simulating 
the table with hard coded data does return the expected results. 


16:55:47 SQL>@j 
16:55:48 SQL> 
16:55:48 SQL> 
16:55:48 SQL>select     v.col1, v.row_eff_date, v.row_term_date 
16:55:48   2  from 
16:55:48   3  (  -- Inline view begins 
16:55:48   4  select 10000151 col1 
16:55:48   5          , to_date('01-jan-1900','dd/mon/yyyy') row_eff_date 
16:55:48   6          , to_date('13-dec-2002','dd/mon/yyyy') row_term_date 

16:55:48   7  from dual 
16:55:48   8  union 
16:55:48   9  select 10000151 col1 
16:55:48  10          , to_date('13-dec- 2002','dd/mon/yyyy') row_eff_date 

16:55:48  11          , to_date('23-jan-2003','dd/mon/yyyy') row_term_date 

16:55:48  12  from dual 
16:55:48  13  union 
16:55:48  14  select 10000151 col1 
16:55:48  15          , to_date('23-jan-2003','dd/mon/yyyy') row_eff_date 
16:55:48  16          , to_date('31-dec-2999','dd/mon/yyyy') row_term_date 

16:55:48  17  from dual 
16:55:48  18  ) v 
16:55:48  19  where 
16:55:48  20  ( 
16:55:48  21          trunc(v.row_eff_date)   > 
to_date('03/14/2004','MM/DD/YYYY')  OR 
16:55:48  22          trunc(v.row_term_date)  = 
to_date('12/31/2999','MM/DD/YYYY') 
16:55:48  23  ) 
16:55:48  24  / 

      COL1 ROW_EFF_DATE        ROW_TERM_DATE 
---------- ------------------- ------------------- 
  10000151 01/23/2003 00:00:00 12/31/2999 00:00:00 

1 row selected. 

16:55:48 SQL> 






david wendelken <davewendelken@xxxxxxxxxxxxx> 
Sent by: oracle-l-bounce@xxxxxxxxxxxxx 
 03/18/2004 04:29 PM 
 Please respond to oracle-l 
        
        To:        oracle-l@xxxxxxxxxxxxx 
        cc:         
        Subject:        RE: Query returning wrong results.



Actually, the query is doing exactly what you told it to do!

The first part of the subquery would bring back each and every row with a 
col1   = '10000151'
.

The second part of the subquery would bring back some of those same rows 
again, but with different date values, thus duplicating some rows in the 
result set.



-----Original Message-----
From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
Sent: Mar 18, 2004 5:38 PM
To: "'oracle-l@xxxxxxxxxxxxx'" <oracle-l@xxxxxxxxxxxxx>
Subject: RE: Query returning wrong results.

Is it using PQO?

Waleed

-----Original Message-----
From: Prasada.Gunda@xxxxxxxxxxxxxxxx
[mailto:Prasada.Gunda@xxxxxxxxxxxxxxxx]
Sent: Thursday, March 18, 2004 2:32 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Query returning wrong results.



Hi,

One of our developer sent me this query that it is returning wrong results 
.
Please see the following query.

select  v.col1, v.row_eff_date, v.row_term_date
from
(  -- Inline view begins
select a.col1
,    trunc(a.col2) row_eff_date
,    nvl(lead(trunc(a.col2)) over
       (partition by a.col1, a.tblnm, a.clmnnm order by a.col2),to_date
('12/31/2999','mm/dd/yyyy')) row_term_date
from   table1 a
where  a.tblnm  = 'INPUT_TABLE'
and    a.clmnnm = 'INPUT_COL'
and    a.col1   = '10000151'
union all
select a1.col1
,    to_date('01/01/1900','mm/dd/yyyy') row_eff_date
,    trunc(a1.col2) row_term_date
from   table1 a1
where  a1.tblnm  = 'INPUT_TABLE'
and    a1.clmnnm = 'INPUT_COL'
and    a1.col1   = '10000151'
and    (a1.col1, a1.col2) IN
               (select octl1.col1, min(octl1.col2) 
                from table1 octl1
                where octl1.tblnm  ='INPUT_TABLE'
                and   octl1.clmnnm ='INPUT_COL'
                group by octl1.col1
                )
-- Inline view ends.
) v
where
(
    trunc(v.row_eff_date)   >  to_date('03/14/2004','MM/DD/YYYY')  OR
    trunc(v.row_term_date)  =  to_date('12/31/2999','MM/DD/YYYY')
)


If I run inline view on its own, it returns 3 records.

COL1         ROW_EFF_DAT ROW_TERM_DA
------------ ----------- -----------
10000151     01-jan-1900 13-dec-2002
10000151     13-dec-2002 23-jan-2003
10000151     23-jan-2003 31-dec-2999

If I run t he entire sql, it suppose to return the following record.

COL1         ROW_EFF_DAT ROW_TERM_DA
------------ ----------- -----------
10000151     23-jan-2003 31-dec-2999

But, the query returns the following two records which is wrong.

COL1         ROW_EFF_DAT ROW_TERM_DA
------------ ----------- -----------
10000151     13-dec-2002 23-jan-2003
10000151     23-jan-2003 31-dec-2999

Did anybody experience this before. I kind of remember that it is 
something
to do with analytical function but I can not recall.
For the purpose of testing, when I created a table out of inner sql and
used that table in the inner query, it works fine. The DB is 8.1.7.4 on
Hp-unix v11.  I also opened a TAR and will update the status to the List.

Thanks in advance. 

Best regards,
Prasad







** ***********************************************************************
PRIVILEGED AND CONFIDENTIAL: This communication, including attachments, is
for the exclusive use of addressee and may contain proprietary, 
confidential
and/or privileged information.  If you are not the intended recipient, any
use, copying, disclosure, dissemination or distribution is strictly
prohibited.  If you are not the intended recipient, please notify the 
sender
immediately by return e-mail, delete this communication and destroy all
copies.
*************************************************************************

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freeli sts.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to:  oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject lin e.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------


---------------------------------------------------------------- Please 
see the official ORACLE-L FAQ: http://www.orafaq.com 
---------------------------------------------------------------- To 
unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 
'unsubscribe' in the subject line. -- Archives are at 
//www.freelists.org/archives/oracle-l/ FAQ is at 
//www.freelists.org/help/fom-serve/cache/1.html 
----------------------------------------------------------------- 

Other related posts: