Re: another believe or not... -0 (negative zero)

Hi Jonathan,

I did a little more work on it today and when I do a dump(convert(col, 'us7ascii', 'we...NT char set'),16), I get either 2d,30 or 30. Low and behold 2d is ascii 45 "-" and 30 is ascii 48 "0". That's what seems to be stored there. I'm starting to think it's some kind of NLS conversion problem between Oracle and Peoplesoft's SQR module. Regardless, Oracle should be able to convert it properly or error out.

Thanks,
Steve

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote in message news:942175766.11379.0.nnrp-01.9e984b29_at_news.demon.co.uk...
>
> Well
>
> 1 should be C1 02
> -1 should be 3E, 64, 66
>
> So your C1,1 and 3E,65,66 seem to be
> a pair of new integers between -1 and 1.
>
> Have you used an old version 5 executable
> against an Oracle 8.
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Steve Perry wrote in message <807tba$mtj$1_at_nntp2.atl.mindspring.net>...
> >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)
> >
>
>---------------------------------------------------------------------------
> -
> >-----
> >select rowid
> > , 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 Tue Nov 09 1999 - 17:28:18 CST