Re: Query returning wrong results.

From: <Jared.Still_at_radisys.com>
Date: Thu, 18 Mar 2004 13:31:06 -0800
Message-ID: <OFBFBC93CE.3A113CA6-ON88256E5B.0075DE57-88256E5B.00761E32@radisys.com>

Looks like a bug. If you provide some platform and version information someone may have an idea as to which bug.

Better yet, check on MetaLink for related bugs.

If it is a parallel query it could be bug 1759227

Jared

Prasada.Gunda_at_hartfordlife.com
Sent by: oracle-l-bounce_at_freelists.org
 03/18/2004 11:31 AM
 Please respond to oracle-l  

        To:     oracle-l_at_freelists.org
        cc: 
        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_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Thu Mar 18 2004 - 15:38:47 CST