Oracle-L: possible Bug in Oracle 9.2.0.2

From: Browett, Darren <dbrowett_at_coquitlam.ca>
Date: Wed, 06 Aug 2003 15:34:22 -0800
Message-ID: <F001.005C94BD.20030806153422@fatcity.com>

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). Received on Wed Aug 06 2003 - 18:34:22 CDT