Oracle-L: RE: possible Bug in Oracle 9.2.0.2

From: Browett, Darren <dbrowett_at_coquitlam.ca>
Date: Thu, 07 Aug 2003 10:49:29 -0800
Message-ID: <F001.005C97EC.20030807104929@fatcity.com>

That fixed it, thank you.

I still haven't heard from oracle support yet.

-----Original Message-----
Sent: Thursday, August 07, 2003 4:44 AM
To: Multiple recipients of list ORACLE-L

You it is a bug ....

alter session|system set "_unnest_subquery"=false /

Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art !

-----Original Message-----
Sent: Wednesday, August 06, 2003 7:34 PM To: Multiple recipients of list ORACLE-L

While I am waiting for oracle support to respond to my tar update (2nd callback) I am just wondering if anybody has found this problem.

We have the following select query (from a peoplesoft implementation)

SELECT a.emplid, a.effdt
FROM PS_JOB A
WHERE A.EFFDT = (SELECT MAX(A1.EFFDT) FROM PS_JOB A1 WHERE A.EMPLID = A1.EMPLID AND A.EMPL_RCD = A1.EMPL_RCD AND A1.EFFDT <= SYSDATE) AND A.EFFSEQ = (SELECT MAX(A2.EFFSEQ) FROM PS_JOB A2 WHERE A.EMPLID = A2.EMPLID AND A.EMPL_RCD = A2.EMPL_RCD AND A.EFFDT = A2.EFFDT) AND A.EMPL_STATUS = 'A'
and a.emplid='3442'

when we run the query we get one row back, but when we replace the field

names with count(*), the resulting answer back is "2". We have tested it in 8.0.5.1.1 and we get the correct results, 1 row, and a count of 1.

Darren

Darren Browett P.Eng                                            This 
message was transmitted 
Data Administrator                                              using

100% recycled electrons
Information and Communication Technology City of Coquitlam
P:(604)927 - 3614
E:dbrowett_at_coquitlam.ca

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Browett, Darren
INET: dbrowett_at_coquitlam.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services 
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Browett, Darren
INET: dbrowett_at_coquitlam.ca

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------