| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> another believe or not... -0 (negative zero)
Ok. I have another one.
Thanks for all the help on the lock. It all made sense
This is a little more complicated. Look at the table definition for
ps_ledger.
I'm running Oracle 8.0.5 on AIX 4.3.2 when I got called because every time
they did a select with a group by, they were getting end of file
communication errors (ora-3113). This was running fine before, but all of
sudden started choking. Oracle support recognized this as a known bug and
that we should upgrade to version 8.0.5.2.1. We did, but continued to get
the error. After some time, I figured out we could put the summed column in
the group by sql and do a "to_number" on it inside the sum (i.e.
sum(to_number(posted_total_amt))) and it would work. I kept trying to figure
out a way to find out which rows out of 8 million were causing the problem
and came up with the sql below.
Take a look at the table definition and the values below. Has anyone run
into this one - NEGATIVE ZERO????
I assume Oracle protected the integrity of the data, so I'm confused. I
exported and imported the table and it worked just fine. I've dbv'd the
files and all is ok.
Can anyone explain how this could happen? Can anyone explain what the real
values in the column are?
The last I heard, Oracle was going to put it down as a new bug.
Thanks for any help or advice on what I can do to research it a bit more.
Steve
SQL> desc ps_ledger
Name Null? Type ------------------------------- -------- ---- BUSINESS_UNIT NOT NULL VARCHAR2(5) LEDGER NOT NULL VARCHAR2(10) ... ACCOUNTING_PERIOD NOT NULL NUMBER(38) POSTED_TOTAL_AMT NOT NULL NUMBER(15,2) POSTED_BASE_AMT NOT NULL NUMBER(15,2) BASE_CURRENCY NOT NULL VARCHAR2(3) DTTM_STAMP_SEC DATE PROCESS_INSTANCE NOT NULL NUMBER(10) ----------------------------------------------------------------------------
, posted_total_amt
, to_number(posted_total_amt) conv
, dump(to_number(posted_total_amt), 16) hex
, dump(posted_total_amt, 16) hex
from ps_ledger
where ledger = 'PRIMARY' and
business_unit in ('001', '018', '080', '101')
and fiscal_year = 1999
and (dump(to_number(posted_total_amt), 16) != dump(posted_total_amt, 16) )
/
POSTED
ROWID TOTAL_AMT CONV HEX HEX
------------------ --------- ------ ------------------ ---------------------
AAAPYIABcAAAJZwAAb ???-> -0 0 Typ=2 Len=1:80 Typ=2 Len=3: 3e,65,66
AAAPYIABcAAAJZ3ABG 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAb 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAc 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAd 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAe 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAf 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAg 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAh 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAi 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAj 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAk 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAl 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAm 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAX 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAY 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAZ 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
AAAPYIABcAAAJZ7AAa 0 0 Typ=2 Len=1:80 Typ=2 Len=2: c1,1
By the way, 80 is the correct value of the dump. I'm not sure what c1,1 is. The negative 0 is a good one. Received on Mon Nov 08 1999 - 19:31:36 CST
 |
 |