All Forums Database
yuvana 7 posts Joined 09/14
12 May 2015
ERROR IN CASTING DECIMAL TO INTEGER

hi I have a  TABLE1  with this structure 

CREATE  MULTISET TABLE DB1 .TABLE1 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      

     

   

      PRODUCT_NBR INTEGER

)

 

I have another Table2 with this structure 

CREATE  MULTISET TABLE DB2 .TABLE2 ,NO FALLBACK ,

     NO BEFORE JOURNAL,

     NO AFTER JOURNAL,

     CHECKSUM = DEFAULT,

     DEFAULT MERGEBLOCKRATIO

     (

      

     

   

      PRODUCT_NBR UPC_NBR DECIMAL(14,0) NOT NULL COMPRESS (2200000512. ,3700016896. ,1. ,7072000513. ,40000004865. ,2. ,2200000514. ,4000000003. ,40000004869. ,5042811398. ,4177153798. ,449542. ,7410098184. ,407560. ,4900001801. ,3700033547. ,61126981899. ,7336070925. ,3400004878. ,5200032016. ,2820000784. ,5042870803. ,5042807571. ,40000004883. ,5042827029. ,7800008216. ,5042807577. ,3457587996. ,4000000031. ,4000000032. ,2820000801. ,7097044770. ,1060. ,4000015140. ,4900000551. ,78616201000. ,3600028200. ,4000000296. ,5042808618. ,3800000810. ,4900000044. ,5042838572. ,4900000045. ,9005. ,4900000046. ,3400008752. ,7800008240. ,5042838577. ,9010. ,5042805555. ,4000000051. ,2820000819. ,7800008246. ,3700013878. ,78616211000. ,5200024121. ,5200024123. ,730683. ,7084781116. ,90000099900. ,61126999100. ,4133341501. ,5400016447. ,1070006080. ,3700035907. ,9030. ,7084781126. ,3500000839. ,5042804808. ,78616233800. ,5200032585. ,5042815561. ,4900002890. ,3400000330. ,4900002891. ,4900002892. ,5400010060. ,7800011340. ,3600067662. ,5042882895. ,5042800721. ,5200033875. ,5042820947. ,3400008788. ,5200033876. ,5200033877. ,40000000344. ,5042807897. ,40000002649. ,61300871771. ,4190002012. ,90088599900. ,6827493471. ,1120. ,1200080994. ,1200080995. ,5400044132. ,9060. ,1200080996. ,4000000101. ,2820000357. ,2820000869. ,61300871526. ,4000000102. ,4000000105. ,5042828905. ,5042801258. ,7572043115. ,2820000363. ,7072000109. ,40000011377. ,40000003700. ,4900002934. ,5042864503. ,3400040568. ,6700000890. ,3700007548. ,3400007038. ,4900000639. ,5042805887. ,78616208000. ,2820000384. ,2840008321. ,1200000129. ,9090. ,1200000130. ,3400054402. ,7572033411. ,1200000131. ,5042810245. ,7160000901. ,90000099974. ,1200000134. ,4900003719. ,1230000007. ,1600027527. ,2800001159. ,40000005511. ,5400042120. ,90000099976. ,40000005512. ,78616220043. ,3400017038. ,90000099982. ,7800015246. ,4000000144. ,4900005010. ,4900005011. ,90000099987. ,4460002452. ,7800008340. ,5042803349. ,452501. ,5042861206. ,4900000663. ,5042807447. ,4900005015. ,5042808215. ,90000099993. ,7572000409. ,5042803866. ,9451441050. ,5042861211. ,5042804892. ,9451441052. ,3700028316. ,157. ,9451441053. ,1200000157. ,4000023454. ,4900004255. ,4000023455. ,1200000159. ,4000000160. ,5200032673. ,5042811555. ,3700028839. ,5042854312. ,5042832553. ,3400000686. ,5200032431. ,3700000175. ,5042861231. ,4000000432. ,3500049840. ,5042804657. ,3700000177. ,3700010673. ,2840007858. ,3700000178. ,5042878645. ,3700000181. ,4127197110. ,452535. ,3400000440. ,5042800572. ,2610000573. ,9150. ,2610000575. ,3700038592. ,2840007874. ,3700000194. ,61300871876. ,5042809285. ,5042823110. ,8813099463. ,8813099464. ,89059200200. ,7036000200. ,2840007881. ,9148600009. ,5042854349. ,3600028110. ,5042826447. ,4900000977. ,2820000465. ,1070080722. ,5042869460. ,78616215000. ,3400017115. ,61126910171. ,2820000477. ,4900004574. ,2820000478. ,4900004575. ,3400000480. ,2200000483. ,5100001251. ,2200000484. ,2200000486. ,1200000230. ,1200000231. ,5042807528. ,2200000488. ,5042807529. ,1200000233. ,1005. ,7036000237. ,4400000750. ,3400000239. ,3400000240. ,3400000241. ,1010. ,2410022642. ,4900004086. ,4400000758. ,3400000246. ,7504400120. ,471545. ,61300871930. ,5042807547. ,4177833467. ,4900000764. ,45042800124. ,99999999. )

)

 

 

 

NOW the error I am getting is 

 

When I am trying to insert this PRODUCT_NBR column from DB2.TABLE2 to DB1.TABLE1

I am getting  the error as 'INSERT Failed. 2616:Numeric overflow occurred during computation.

 

I Tried CAST(CAST(upc_number AS CHAR(10))  AS INTEGER) but, the same error message its showing.

 

Can any one help me to cast it .

 

 

Thank you

Yuvana

 

dnoeth 4628 posts Joined 11/04
12 May 2015

Well, a decimal has a range of +/+ 2**31, of course a 14-digit decimal doesn't fit.

Dieter

yuvaevergreen 93 posts Joined 07/09
13 May 2015

Try bigint

yuvana 7 posts Joined 09/14
13 May 2015

is there any possibility to cast it .
 
Hi Dietier.
 
Thank you for reply. Is there any possibility to cast it
 
Yuvana

dnoeth 4628 posts Joined 11/04
13 May 2015

Hi Yuvana,
of course there's no way to cast a 14 digit number to an integer (and stripping of some digits by casting to a varchar(10) will result in garbage).
You need to change the target table definition to either DEC(14,0) or BIGINT.

Dieter

yuvana 7 posts Joined 09/14
13 May 2015

Thank you Dietier.
 
Yuvana

You must sign in to leave a comment.