How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666764] |
Fri, 24 November 2017 02:28 |
|
ssyr
Messages: 65 Registered: January 2017
|
Member |
|
|
Hi All,
I am using one insert statement that is selecting data form other table. But when I tried to execute insert statement it is giving me below error,
"ORA-01438: value larger than specified precision allowed for this column".
In my query how can I modify that column to actual precision .I dnot want to go for alter table command.
insert stg_rw_
select
,cast(oi.quantity as number(20)) quantity
,cast(nvl(oi.quantity*oi.unit_priceoi.discount,0) as number(38,8)) usd_price_num
,cast(nvl(oi.quantity*oi.unit_priceoi.discount,0) * nvl(o.currency_from_usd_rate,1) as number(38,8)) tx_price_num
,o.currency tx_currency_code
,o.vreferrer aid
from orders o
join order_items oi
on o.orderid = oi.orderid ;
I have to used in cast operator.
Current size and data type of columns are,
QUANTITY NUMBER(10,0)
TX_PRICE_NUM NUMBER(38,8)
USD_PRICE_NUM NUMBER(38,8)
HOw can I truncate it to precision point.
Could you please suggest me some things on below?
Thank you in Advance.
[LF applied CODE tags to table description]
[Updated on: Fri, 24 November 2017 02:52] by Moderator Report message to a moderator
|
|
|
|
|
|
|
|
|
|
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666775 is a reply to message #666772] |
Fri, 24 November 2017 06:09 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Trunc is zero use here.
You don't get that error when you have too many decimals, which trunc could fix (though so could round and it's more obvious).
You get that error when you have too many non-decimals.
Say if you try to insert 1000 into a number(2).
Trunc won't make 1000 into a 2 digit number, nor will cast - it'll throw the same error.
The only sensible solution is modify the columns so they are big enough, anything else is just corrupting data.
|
|
|
|
|
|
|
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666811 is a reply to message #666809] |
Mon, 27 November 2017 00:17 |
|
Littlefoot
Messages: 21818 Registered: June 2005 Location: Croatia, Europe
|
Senior Member Account Moderator |
|
|
In NUMBER(5, 2), 5 is called "precision" (total number of digits) and 2 is called "scale" (number of digits to the right of the decimal point). You can read it as a column which accepts numbers which have total of 5 digits, whose 2 digits are decimals which means that only 3 digits are left for the whole part of a number. Therefore, you can store 123.45 in there, but you can't store 1234.56.
In VARCHAR2(4 BYTE), you can store 4 bytes. How many characters? It depends on how many bytes those characters take. There are single-byte characters (which occupy 1 byte, so you can store 4 characters), but there are also multi-byte characters (such as in Chinese character set) where "1 characters <> 1 byte".
Have a look at Oracle datatypes which explains it far better than me.
|
|
|
|
|
|
|
|
|
Re: How to Avoid ORA-01438: value larger than specified precision allowed for this column [message #666822 is a reply to message #666821] |
Mon, 27 November 2017 03:55 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The only time it can make sense to use cast on a number to make it number is when you are doing a create table as select.
SQL> create table bob as select rownum a, cast(1 as number(10)) b from dual;
Table created.
SQL> desc bob;
Name Null? Type
----------------------------------------- -------- ----------------------------
A NUMBER
B NUMBER(10)
SQL>
It's used the cast to define the column size.
The way you're using it doesn't make sense. Source and destination size are known.
If the number you are trying to cast is > 20 digits then the cast will throw the error.
If the number is between 11 and 20 digits then the insert will throw the error.
If the number is greater than 10 digits then what do expect it to be transformed into to fit in a number(10)?
If you can't come up with a sensible answer to that (and I very much doubt there is a sensible answer to that) then you need to increase the size of the number 10.
Unless the too large data is erroneous - in which case you need to find that wrong data and fix it in the source.
|
|
|
|
|
|