RE: Query returning wrong results.
- From: david wendelken <davewendelken@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 18 Mar 2004 19:29:45 -0500 (GMT-05:00)
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 the 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 //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
-----------------------------------------------------------------
----------------------------------------------------------------
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:
