Laura L."To: Multiple recipients of
> list ORACLE-L <[EMAIL PROTECTED]>
>
> @prismplus.coSubject: RE: To_Number
>
> m>
>
> Sent by: root
>
>
>
>
>
>
gt;
>
>
>
>
> "Burton,
>
> Laura L."To: Multiple recipients of
> list ORACLE-L <[EMAIL PROTECTED]>
>
> @prismplus.coSubject: R
It's obvious they are using Oracle as a glorified fileing system.
JD Edwards does the same thing. They create tables with columns with things
like Number(38). they store the number with no decimal points. the "middle
layer" has the definition of the column. so all applications have to call
t
That's probably why it's in a VARCHAR.
"Correct the data"? What strange ideas you mortals have.
"Burton,
Title: RE: To_Number
Thanks for the email!! I did not know about Dump so I learned something new. The record I thought I had a problem with was ok and that is why I did not see anything amiss. However the record after this one was $20041-94. Corrected that and got $145.34 EA. I just told
I always ask the obvious questions... because the answer is usually
"oops we screwed up" and it fixes the problem
I STILL think "because" is an unacceptable answer to "why did you make
a number field varchar?"
--- "Mercadante, Thomas F" <[EMAIL PROTECTED]> wrote:
> Oh sure. you had to ask the ob
Texas
512.414.9715 (wk)
512.935.5929 (pager)
[EMAIL PROTECTED]
Rachel
Carmichael To: Multiple recipients of list
ORACLE-L
AHOO.COM>cc:
Sent by
Oh sure. you had to ask the obvious question and start a holy war!
Tom Mercadante
Oracle Certified Professional
-Original Message-
Sent: Thursday, December 05, 2002 4:50 PM
To: Multiple recipients of list ORACLE-L
Am I the only one wondering why an obviously numeric field
(unit_cost??
Look at NLS_NUMERIC_CHARACTERS " ,. "
-Message d'origine-
De: Jared Still [mailto:[EMAIL PROTECTED]]
Date: vendredi 6 décembre 2002 09:59
À: Multiple recipients of list ORACLE-L
Objet: Re: To_Number
No, she isn't.
On Thursday 05 December 2002 14:19, Fink, Dan wrote:
> Yes, yes you are.
>
> -Original Message-
> Sent: Thursday, December 05, 2002 2:50 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Am I the only one wondering why an obviously numeric field
> (unit_cost???) is being
Did you check it with dump() ?
select dump(unit_cost,16) from tablewhatever;
for Hex. Check for unprintables that way. Try inserting the bad values
into a look-aside table for later analysis i.e.
create table look_aside (rownum rowid, bad_val varchar2(20),
dump_val varchar2(200));
declare
b
motto.;
> -)
>
> Jack C. Applewhite
> Database Administrator
> Austin Independent School District
> Austin, Texas
> 512.414.9715 (wk)
> 512.935.5929 (pager)
> [EMAIL PROTECTED]
>
>
>
>
>
> Rachel
>
> Carmichael
Title: RE: To_Number
Since we don't have that many 3rd party software packages I did make the mistake of asking 'Why??' and received 'Because!' so I too am trying to jump in and 'fix it'. As I responded to another email earlier, the RTrim worked because
Rachel
Carmichael To: Multiple recipients of list ORACLE-L
AHOO.COM>cc:
Sent by:
Yes, yes you are.
-Original Message-
Sent: Thursday, December 05, 2002 2:50 PM
To: Multiple recipients of list ORACLE-L
Am I the only one wondering why an obviously numeric field
(unit_cost???) is being stored as varchar?
--- [EMAIL PROTECTED] wrote:
>
> Laura,
>
> Are those really z
: Multiple recipients of list
ORACLE-L
Subject: RE: To_Number
My guess is that you have
leading or trailing spaces. try
select to_number(LTRIM(RTRIM(unit_cost)),'$999,999,999.99')
from elas.qdr
-Original Message-
From: Burton, Laura L.
[mailto:[EMAIL PROTECTED]]
Sent
Am I the only one wondering why an obviously numeric field
(unit_cost???) is being stored as varchar?
--- [EMAIL PROTECTED] wrote:
>
> Laura,
>
> Are those really zeros in $34,000.05 or are they letter Os? If so
> use
> Replace. (Beware of letter l being used instead of numeral 1 as
> well.)
Laura,
Are those really zeros in $34,000.05 or are they letter Os? If so use
Replace. (Beware of letter l being used instead of numeral 1 as well.)
Any leading or trailing spaces? If so use Trim(unit_cost).
Just a couple of quick suggestions.
Jack C. Applewhite
Database Administrator
Austin
Title: RE: To_Number
Very Possible. I'm 8.1.7.2 W2K sp2
-Original Message-
From: Kevin Lange [SMTP:[EMAIL PROTECTED]]
Sent: Thursday, December 05, 2002 3:30 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: To_Number
Lisa;
I wonder if it depends o
Title: To_Number
My
guess is that you have leading or trailing spaces. try
select
to_number(LTRIM(RTRIM(unit_cost)),'$999,999,999.99') from
elas.qdr
-Original Message-From: Burton, Laura L.
[mailto:[EMAIL PROTECTED]]Sent: Thursday, December 05, 2002
2:24 PMTo: Multiple recipi
Title: To_Number
Laura,
Works
for me:
SQL> select unit_cost,
to_number(unit_cost,'$999,999.99') from tomtest;
UNIT_COST
TO_NUMBER(UNIT_COST,'$999,999.99')
--$34,100.50
34100.5$34,000.
Title: RE: To_Number
Lisa;
I wonder if it depends on the DB version. I did this on an 8.0.5 and
got the invalid number error running the exact query that succeeded on
yours. I had to do a replace on both the commas and the dollar
sign.
-Original Message-From: Koivu, Lisa
Title: To_Number
I
believe its the $ and , in the data. I got the same error until I
did
select
to_number(replace(replace(unit_cost,'$',''),',','')) from
elas.qdr
-Original
Message-From: Burton, Laura L.
[mailto:[EMAIL PROTECTED]]Sent: Thursday, December 05, 2002 1:24
PMTo: Mu
Title: RE: To_Number
Laura are you sure you aren't trying this with the quotes? See below
SQL> select * from testnum;
COL1
---
$24,990.09
SQL> select to_number('col1','$999,999,999.99') from testnum;
select to_number('col1','
24 matches
Mail list logo
