What about number(1,0)?
Doug Cowles, October 20, 2001 - 2:29 pm UTC
SQL> create table a (a number(1,0));
Table created.
SQL> insert into a values (1);
1 row created.
SQL> insert into a values (1.9);
1 row created.
SQL> select * from a;
A
----
1
2
SQL>
That rounds it. Truncate doesn't round.. No?
October 20, 2001 - 2:37 pm UTC
that doesn't do what they asked to do:
"...and if i try to insert value as 9.9
then it will make it 10 and then insert into the database instead i want to
truncate the extra digit."
yours rounds, they want trunc
I wanted to change the settings of Oracel
Keval Shah, October 22, 2001 - 3:53 am UTC
I have got n number of tables and n number of columns which is having datatype as number and i wanted to change the setting for all of them at 1 shot.
Thanx anyway to responding my question.
Numeric Value Precision too large
Carlton Gregory, December 14, 2004 - 10:22 am UTC
Tom,
Im trying to figure out why the column unit_cost is causing a precision too large error.
How does NVL determine the precision? I think this is a rounding issue like the previous person has.
If I make unit_cost in the first query a literal zero I get no errors.
SELECT ai.item_id AS item_id,
m.nomenclature AS nomenclature,
m.item_type AS item_type,
m.shelf_life_code AS shelf_life_code,
ai.ammal_id AS ammal_id,
w.account_name AS wc_supply_name,
DECODE( mm_lib.get_bed_level,
'1000', ai.fos_1000_qty,
'500', ai.fos_500_qty,
'250', ai.fos_250_qty,
ai.local_qty ) AS local_qty,
NVL(mm_lib.calc_ammal_qob(ai.item_id, ai.ammal_id, ai.account_seq),0) AS ammal_qob,
NVL(mm_lib.calc_qob(ai.item_id),0) AS total_qob,
NVL(mm_lib.calc_sub_ammal_qob(ai.item_id, ai.ammal_id, ai.account_seq),0) AS sub_ammal_qob,
NVL(mm_lib.calc_sub_qob(ai.item_id),0) AS sub_total_qob,
NVL(mm_lib.calc_ammal_qoo(ai.item_id, ai.ammal_id, ai.account_seq),0) AS on_order,
NVL(mm_lib.calc_sub_ammal_qoo(ai.item_id, ai.ammal_id, ai.account_seq),0) AS sub_on_order,
u.uoi_code AS inv_uoi_code,
u.unit_price AS unit_price,
mm_lib.req_number_string(ai.account_seq, ai.ammal_id, ai.item_id, 'SAMS') AS sams_req_num_string,
mm_lib.req_number_string(ai.account_seq, ai.ammal_id, ai.item_id, 'SNAP') AS snap_req_num_string,
NVL(ai.required_qty,0) AS required_qty,
NVL(ai.low_order_qty,0) AS low_order_qty,
NVL(mm_lib.get_totals(w.account_name, ai.item_id),0) AS unit_cost
FROM mm_account w,
mm_ammal_item ai,
mm_item_master m,
mm_item_uoi u
WHERE ai.item_id = m.item_id
AND ai.account_seq = w.account_seq
AND m.item_id = u.item_id (+)
AND m.req_item_level = u.uoi_level (+)
UNION
SELECT ai.item_id,
m.nomenclature,
m.item_type,
m.shelf_life_code,
ai.ammal_id,
w.account_name,
DECODE( mm_lib.get_bed_level,
'1000', ai.fos_1000_qty,
'500', ai.fos_500_qty,
'250', ai.fos_250_qty,
ai.local_qty ),
NVL(mm_lib.calc_ammal_qob(ai.item_id, ai.ammal_id, ai.account_seq),0),
NVL(mm_lib.calc_qob(ai.item_id),0),
NVL(mm_lib.calc_sub_ammal_qob(ai.item_id, ai.ammal_id, ai.account_seq),0),
NVL(mm_lib.calc_sub_qob(ai.item_id),0),
NVL(mm_lib.calc_ammal_qoo(ai.item_id, ai.ammal_id, ai.account_seq),0),
NVL(mm_lib.calc_sub_ammal_qoo(ai.item_id, ai.ammal_id, ai.account_seq),0),
u.uoi_code,
u.unit_price,
mm_lib.req_number_string(ai.account_seq, ai.ammal_id, ai.item_id, 'SAMS'),
mm_lib.req_number_string(ai.account_seq, ai.ammal_id, ai.item_id, 'SNAP'),
NVL(ai.required_qty,0),
NVL(ai.low_order_qty,0),
NVL(mm_lib.get_totals(w.account_name, ai.item_id),0)
FROM mm_account w,
mm_ammal_item ai,
mm_item_master m,
mm_item_uoi u,
mm_item_xref ix
WHERE ai.item_id = m.item_id
AND ai.account_seq = w.account_seq
AND m.item_id = u.item_id (+)
AND m.req_item_level = u.uoi_level (+)
AND m.item_id = ix.xref_item_id
AND ix.xref_type = 'S';
December 14, 2004 - 11:03 am UTC
hmm, deja-vu all over again.
just saw this on the newsgroups. my thoughts when I saw it there was "without a test case, without an actual error message, and without a smaller example, this is never going to get answered..."
probably -- the error isn't even in sql perhaps. maybe it is your custom plsql code.
but without the actual error message, a way to reproduce -- no one can say for sure.
The specific error was
Carlton Gregory, December 14, 2004 - 3:43 pm UTC
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "SAMS.MM_LIB", line 716
ORA-06512: at line 1
ORA-06512: at "SAMS.MM_LIB", line 796
ORA-06512: at line 1
December 15, 2004 - 1:02 pm UTC
yes, so the error is in your PLSQL code it looks like.
what are lines 796 and 716 -- query user_source, see what they are.
sounds like you defined a number type too small in your code and it has nothing to do with the SQL query -- in as much as the data type you are assigning to.
To Carlton
Menon, December 14, 2004 - 8:33 pm UTC
You get this error when the PL/SQL variable where
you store the result is too small. Check
the precision of the variable where your offending
column (NVL(mm_lib.get_totals(w.account_name, ai.item_id),0)?) is getting stored:
----
benchmark@ORA92I> declare
2 l_count number(2);
3 begin
4 select nvl( to_number(rpad('1',100,'1') ) , 0 )
5 into l_count
6 from dual;
7 end;
8 /
declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at line 4
----------
Understood
Carlton Gregory, December 15, 2004 - 9:22 am UTC
Menon,
I understand what the error is saying but it (my function call) is not explicitly passing a value to a variable as you did in your example.
The function is returning a value for a column in view.
So there are no constraints on that column.
The constraints of the column are being defined by the value returned from the function.
Like Tom said most likely it has something to do with the return value of the function I call.
To Carlton:
Menon, December 15, 2004 - 11:23 am UTC
Hi Carlton,
You mean something like below? (in which case
it is the same concept essentially.)
benchmark@ORA92I> create or replace function f return integer
2 as
3 l_count number(2);
4 begin
5 select nvl( to_number(rpad('1',100,'1') ) , 0 )
6 into l_count
7 from dual;
8 return l_count;
9 end;
10 /
Function created.
benchmark@ORA92I> select nvl( f, 0 ) from dual;
select nvl( f, 0 ) from dual
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: number precision too large
ORA-06512: at "BENCHMARK.F", line 5
ORA-06502: PL/SQL: numeric or value error: character to number conversion error
Jagadeesh Tata, November 28, 2005 - 6:20 am UTC
Hi Tom,
I was strucked with a simple problem,following are the statements.
I declared following variable
l_adjustment_amount VARCHAR2(1000);
All the following columns are of type varchar2(150).
l_servicerequest1_rec.incident_attribute_10,
l_servicerequest1_rec.incident_attribute_11,
l_servicerequest1_rec.incident_attribute_12.
The above variables contain floating values
ex:10.2
I used following statement in my procedure.
l_adjustment_amount := NVL(l_servicerequest1_rec.incident_attribute_10,'0') +
NVL(l_servicerequest1_rec.incident_attribute_11,'0') +
NVL(l_servicerequest1_rec.incident_attribute_12,'0');
The above statement is leading to error.
ORA-06502: PL/SQL: numeric or value error: character to number conversion error.
Using the same logic I created a sample program and it is working fine.
declare
a varchar2(100) := 100;
b varchar2(100);
c varchar2(100);
d varchar2(100);
begin
d := nvl(a,'0') + nvl(b,'0') + nvl(c,'0');
dbms_output.put_line(d);
exception
when others then
dbms_output.put_line('error');
end;
Iam unable to trace why the first program is routing to error.
Thanks in advance.
November 28, 2005 - 7:44 am UTC
you give me no test case I can run. I have to assume that one of your strings is not a number.
This is the reason we use numbers - to store numbers. Strings are good for storing strings, not good for storing numbers and dates.
Numbers are good for storing numbers, not dates.
Dates are good for storing dates though.
Pro-rating a master across detail records
VA, March 20, 2006 - 11:58 am UTC
SQL> create table m(pk int primary key,amt number(10,2) not null,tot integer not null);
Table created.
SQL> create table d(pk int primary key,fk int not null references m,amt integer not null);
Table created.
SQL> insert into m values (1,10,100);
1 row created.
SQL> insert into d values (1,1,3);
1 row created.
SQL> insert into d values (2,1,3);
1 row created.
SQL> insert into d values (3,1,4);
1 row created.
SQL> select m.pk,m.amt,m.tot,round(m.tot*d.amt/m.amt,2) pro
from m,d where m.pk=d.fk;
PK AMT TOT PRO
---------- ---------- ---------- -----------------
1 10 100 30
1 10 100 30
1 10 100 40
How can I pro-rate that amt=$10 on the master records across in proportion to the detail amt on the detail records and not lose any cents due to rounding error?
Everything is fine in the small example above, but when I have hundreds of detail records, the ROUND kicks in and the SUM(PRO) doesn't equal M.TOT because of the rounding.
I would like the "last" (ordered by some field on the detail) record to get the remainder of the amount, so if we are pro-rating $10 and the running total of the pro-rated amount is $9.98, give the last guy the extra 2c.
Is this possible using analytic functions?
Thanks
March 22, 2006 - 1:00 pm UTC
why would you give me an example where the rounding doesn't happen!!!! arg. How can you develop test cases without the boundary (eg: MOST IMPORTANT) conditions?
but insufficient data here - looks like you have an implied business rule perhaps that says the sum of d.amt by fk equals m.amt (which begs the question, why do you have m.amt at all?)
so, make up example that needs help rounding - doesn't make sense to work with anything else.
VA, March 22, 2006 - 8:41 pm UTC
Sorry about that. Let me just try to explain it in words.
Consider the classic order/order_detail master-detail tables. There is an order for $10 for 9 widgets. There are 3 line-items in the order, each for 3 widgets. I need to pro-rate the $10 across the 3 line-items in proportion of each line item's widgets.
If I use round(10*3/9,2) for each of them, I total up to 9.99 i.e. because of rounding, I lose that final penny!
How can I make it such that the first 2 line items get $3.33 and the last one gets $3.34
Thanks
March 23, 2006 - 10:10 am UTC
make up the example please. tables, data.
VA, March 23, 2006 - 11:45 pm UTC
I thought I already did. Here you go
SQL> create table m(pk int primary key,amt number(10,2) not null,tot integer
2 not null);
Table created.
SQL> create table d(pk int primary key,fk int not null references m,amt integer
2 not null);
Table created.
SQL> insert into m values (1,10,9);
1 row created.
SQL> insert into d values (1,1,3);
1 row created.
SQL> insert into d values (2,1,3);
1 row created.
SQL> insert into d values (3,1,3);
1 row created.
SQL> select m.pk,m.amt,m.tot,round(m.amt*d.amt/m.tot,2) from m,d where m.pk=d.fk;
PK AMT TOT ROUND(M.AMT*D.AMT/M.TOT,2)
---------- ---------- ---------- --------------------------
1 10 9 3.33
1 10 9 3.33
1 10 9 3.33
I lost a penny (10-3.33*3) due to the rounding.
How can I get the rows above to add up to $10 but still round to 2 decimal places (these are dollar amounts so anything with more than 2 decimal places doesn't make sense)
Thanks
March 24, 2006 - 9:35 am UTC
... I thought I already did. Here you go ...
I didn't see it anywhere on this page before - do you?
Ok, the column TOT in M - doesn't belong, that is just the sum of amt in D by FK. It is redundant, I won't use it - you can, but I would suggest getting rid of it all together.
Here is the beginning, we use ratio_to_report by d.amt to see what percentage should be assigned and assign that amount, similar to your query above, but I also use lead to see when we are at the "last row" in the partition:
ops$tkyte@ORA10GR2> select d.pk,
2 d.fk,
3 ratio_to_report(d.amt) over (partition by d.fk) rtr,
4 m.amt,
5 round(ratio_to_report(d.amt) over (partition by d.fk) * m.amt,2) pct,
6 lead(d.pk) over (partition by d.fk order by d.pk) next_pk
7 from d, m
8 where d.fk = m.pk
9 /
PK FK RTR AMT PCT NEXT_PK
---------- ---------- ---------- ---------- ---------- ----------
1 1 .333333333 10 3.33 2
2 1 .333333333 10 3.33 3
3 1 .333333333 10 3.33
ops$tkyte@ORA10GR2> select pk, fk, amt, rtr, pct, sum(pct) over (partition by fk order by pk) running,
2 case when next_pk is NULL
3 then pct + amt - sum(pct) over (partition by fk order by pk)
4 else pct
5 end adjusted
6 from (
7 select d.pk,
8 d.fk,
9 m.amt,
10 ratio_to_report(d.amt) over (partition by d.fk) rtr,
11 round(ratio_to_report(d.amt) over (partition by d.fk) * m.amt,2) pct,
12 lead(d.pk) over (partition by d.fk order by d.pk) next_pk
13 from d, m
14 where d.fk = m.pk
15 )
16 /
PK FK AMT RTR PCT RUNNING ADJUSTED
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 1 10 .333333333 3.33 3.33 3.33
2 1 10 .333333333 3.33 6.66 3.33
3 1 10 .333333333 3.33 9.99 3.34
Now, we can use a running total of the percentage assigned - subtract that from the amount to be assigned and adjust the last row in each window (partition)
Simply brilliant! Thanks a lot.
VA, March 27, 2006 - 8:11 pm UTC
Visual Basic CLng function
jdam, March 07, 2008 - 10:45 am UTC
Hi Tom,
I need to implement in oracle a function like VB.
CLng function definition:
"When the fractional part is exactly 0.5, CInt and CLng always round it to the nearest even number. For example, 0.5 rounds to 0, and 1.5 rounds to 2. CInt and CLng differ from the Fix and Int functions, which truncate, rather than round, the fractional part of a number. Also, Fix and Int always return a value of the same type as is passed in."
Example:
0.5 round to 0
1.5 round to 2
2.5 round to 2
3.5 round to 4
4.5 round to 4
11.5 round to 12
11.6 round to 12
12.5 round to 12
12.6 round to 12
13.5 round to 14
Thanks.
March 10, 2008 - 10:37 am UTC
holy cow, what a "not good function". I've never heard of anything so "not standard"
We, as humans, have been rounding numbers for a good many years, leave it to MS to invent a totally non-standard way of doing it.
Now, your description and your sample output quite simply DO NOT MATCH. As well, a google search of clng finds contradictory information.
http://www.techonthenet.com/access/functions/datatype/clng.php The variable LValue would still contain the value of 35150. Until the fraction is greater than .5, the CLng function will not round the number up.
If the fraction is less than or equal to .5, the result will round down.
If the fraction is greater than .5, the result will round up.
that is different from what you say, but is still WRONG http://msdn2.microsoft.com/en-us/library/ck4c5842(VS.85 ).aspx
that agrees with you written text, disagrees with your example (12.6 rounding to 12 - that would not happen...so, we'll go with the last link there...
ops$tkyte%ORA10GR2> with data1
2 as
3 (select level+0.5 r from dual connect by level <= 12),
4 data2
5 as
6 (select r from data1 union all select r+.1 from data1 union all select r-.1 from data1)
7 select r, case when (r-trunc(r)) = .5 then mod(trunc(r),2)-1 else 0 end + round(r) way1,
8 decode((r-trunc(r)),0.5,mod(trunc(r),2)-1,0) + round(r) way2
9 from data2
10 order by r
11 /
R WAY1 WAY2
---------- ---------- ----------
1.4 1 1
1.5 2 2
1.6 2 2
2.4 2 2
2.5 2 2
2.6 3 3
3.4 3 3
3.5 4 4
3.6 4 4
4.4 4 4
4.5 4 4
4.6 5 5
5.4 5 5
5.5 6 6
5.6 6 6
6.4 6 6
6.5 6 6
6.6 7 7
7.4 7 7
7.5 8 8
7.6 8 8
8.4 8 8
8.5 8 8
8.6 9 9
9.4 9 9
9.5 10 10
9.6 10 10
10.4 10 10
10.5 10 10
10.6 11 11
11.4 11 11
11.5 12 12
11.6 12 12
12.4 12 12
12.5 12 12
12.6 13 13
36 rows selected.
Rounding problem
Deba, May 10, 2011 - 5:33 am UTC
I am getting rounding off problem in below query. I didn’t understand why query is not rounding off properly.
column type as below
APPROVED_EXCH_RATE NUMBER
Original value stored in Table
SQL>
1 select APPROVED_EXCH_RATE
2 from APPROVAL_SNAP
3* where product_nbr=16195
SQL> /
APPROVED_EXCH_RATE
------------------
1.21735
Below value coming after rounding off
SQL> select NVL( TO_CHAR(round(APPROVED_EXCH_RATE,4)), 'null') APPROVED_EXCHANGE_RATE
2 from APPROVAL_SNAP
3 where product_nbr=16195
4 /
APPROVED_EXCHANGE_RATE
----------------------------------------
1.2173
This should be 1.2174
SQL> select round(APPROVED_EXCH_RATE,4) APPROVED_EXCHANGE_RATE
2 from APPROVAL_SNAP
3 where product_nbr=16195
4
SQL> /
APPROVED_EXCHANGE_RATE
----------------------
1.2173
Thanks in advance
Deba
May 10, 2011 - 9:03 am UTC
ops$tkyte%ORA11GR2> create table t ( x number );
Table created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> insert into t (x) values ( 1.21735 );
1 row created.
ops$tkyte%ORA11GR2> insert into t (x) values ( 1.2173499999 );
1 row created.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select x, to_char(x, '999.99999999999' ), round(x,4)
2 from t
3 /
X TO_CHAR(X,'999.9 ROUND(X,4)
---------- ---------------- ----------
1.21735 1.21735000000 1.2174
1.21735 1.21734999990 1.2173
sqlplus, a simple reporting tool, has a default numformat it uses to display data. What you saw in sqlplus with the implicit conversion from number to string must have already been rounded.