ERROR IN CASTING DECIMAL TO INTEGER

Database
Enthusiast

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

5 REPLIES
Junior Contributor

Re: ERROR IN CASTING DECIMAL TO INTEGER

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

Enthusiast

Re: ERROR IN CASTING DECIMAL TO INTEGER

Try bigint
Enthusiast

Re: ERROR IN CASTING DECIMAL TO INTEGER

is there any possibility to cast it .

Hi Dietier.

Thank you for reply. Is there any possibility to cast it

Yuvana

Junior Contributor

Re: ERROR IN CASTING DECIMAL TO INTEGER

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.

Enthusiast

Re: ERROR IN CASTING DECIMAL TO INTEGER

Thank you Dietier.

Yuvana