Board index » delphi » arithmetic exception, numeric overflow, or string truncation Error Help Please

arithmetic exception, numeric overflow, or string truncation Error Help Please

I'm executing a Sp that compiled fine but when I execute it I get this
error.

ISC ERROR CODE:335544321
ISC ERROR MESSAGE:
arithmetic exception, numeric overflow, or string truncation

This is the offending line
ORDER_NO = CAST(D1||D2||D3||D4||D5||D5||D6||D7||D8||D9||D10||Get_Digit AS
INTEGER);
below is a copy of the procedure can someone take look please.
---------------------- Store Procedure ------------------
create procedure BTS
returns (
    ORDER_NO Integer)
as
Declare Variable BTS_Order Integer;
Declare Variable BTS_Order_Char Char(10);
Declare Variable D1 Integer;
Declare Variable D2 Integer;
Declare Variable D3 Integer;
Declare Variable D4 Integer;
Declare Variable D5 Integer;
Declare Variable D6 Integer;
Declare Variable D7 Integer;
Declare Variable D8 Integer;
Declare Variable D9 Integer;
Declare Variable D10 Integer;
Declare Variable Add_Digits Integer;
Declare Variable Get_Digit Integer;

begin
 /* break the order number to calculate the check digit */
 BTS_ORDER = GEN_ID(BTS_ORDER_NO, 1);
 BTS_ORDER_CHAR = CAST(BTS_ORDER AS CHAR(10));
 D1 =  CAST(SUBSTR(BTS_ORDER_CHAR, 1, 1)AS INTEGER);
 D2 =  CAST(SUBSTR(BTS_ORDER_CHAR, 2, 2)AS INTEGER);
 D3 =  CAST(SUBSTR(BTS_ORDER_CHAR, 3, 3)AS INTEGER);
 D4 =  CAST(SUBSTR(BTS_ORDER_CHAR, 4, 4)AS INTEGER);
 D5 =  CAST(SUBSTR(BTS_ORDER_CHAR, 5, 5)AS INTEGER);
 D6 =  CAST(SUBSTR(BTS_ORDER_CHAR, 6, 6)AS INTEGER);
 D7 =  CAST(SUBSTR(BTS_ORDER_CHAR, 7, 7)AS INTEGER);
 D8 =  CAST(SUBSTR(BTS_ORDER_CHAR, 8, 8)AS INTEGER);
 D9 =  CAST(SUBSTR(BTS_ORDER_CHAR, 9, 9)AS INTEGER);
 D10 = CAST(SUBSTR(BTS_ORDER_CHAR, 10, 10)AS INTEGER);
 /* multiply the digits by its weighting factor */
 D4 = D4 * 2;
 D5 = D5 * 1;
 D6 = D6 * 2;
 D7 = D7 * 1;
 D8 = D8 * 2;
 D9 = D9 * 1;
 D10 = D10 * 2;

 /* apply trunc and mod fuctions to get right result */

  IF (D4 > 9) then
 D4 = F_Truncate(MOD(D4,10)/10 + MOD(D4,10));
  IF (D5 > 9) then
 D4 = F_Truncate(MOD(D5,10)/10 + MOD(D5,10));
  IF (D6 > 9) then
 D4 = F_Truncate(MOD(D6,10)/10 + MOD(D6,10));
  IF (D7 > 9) then
 D4 = F_Truncate(MOD(D7,10)/10 + MOD(D7,10));
  IF (D8 > 9) then
 D4 = F_Truncate(MOD(D8,10)/10 + MOD(D8,10));
  IF (D9 > 9) then
 D4 = F_Truncate(MOD(D9,10)/10 + MOD(D9,10));
  IF (D10 > 9) then
 D4 = F_Truncate(MOD(D10,10)/10 + MOD(D10,10));

 Add_Digits = D4+D5+D6+D7+D8+D9+D10;
 Get_Digit = MOD(Add_Digits,10);
 IF (Get_Digit > 0) then
 Get_Digit = 10 - Get_Digit;
 /*Order_No = Get_Digit;*/
 ORDER_NO = CAST(D1||D2||D3||D4||D5||D5||D6||D7||D8||D9||D10||Get_Digit AS
INTEGER);
 exit;
  /*suspend;*/
end
--------------------- end of Procedure ----------------------------
Tia
Charles Urbina

 

Re:arithmetic exception, numeric overflow, or string truncation Error Help Please


I made some changes to the stored procedure and this is the last source code
Also I have found out how the error is triggered.
when I CAST more than 10 digits to the Order_No parameter I get the, but if
I cast 10 or less the sp works great. Any idea why this is happening.

ISC ERROR CODE:335544321
ISC ERROR MESSAGE:
arithmetic exception, numeric overflow, or string truncation

ORDER_NO = CAST(C1||C2||C3||C||C5||C6||C7||C8||C9||C10||C_Digit AS INTEGER);

regards
Charles Urbina
---------------- SP --------
create procedure BTS
returns (
    ORDER_NO integer)
as
Declare Variable BTS_Order Integer;
Declare Variable BTS_Order_Char Char(10);
Declare Variable D Integer;
Declare Variable D1 Integer;
Declare Variable D2 Integer;
Declare Variable D3 Integer;
Declare Variable D4 Integer;
Declare Variable D5 Integer;
Declare Variable D6 Integer;
Declare Variable D7 Integer;
Declare Variable D8 Integer;
Declare Variable D9 Integer;
Declare Variable D10 Integer;

Declare Variable C Char(2);
Declare Variable C1 Char(2);
Declare Variable C2 Char(2);
Declare Variable C3 Char(2);
Declare Variable C4 Char(2);
Declare Variable C5 Char(2);
Declare Variable C6 Char(2);
Declare Variable C7 Char(2);
Declare Variable C8 Char(2);
Declare Variable C9 Char(2);
Declare Variable C10 Char(2);

Declare Variable Add_Digits Integer;
Declare Variable Get_Digit Integer;
Declare Variable C_Digit Char(2);

begin
 /* break the order number to calculate the check digit */
 BTS_ORDER = GEN_ID(BTS_ORDER_NO, 1);
 BTS_ORDER_CHAR = CAST(BTS_ORDER AS CHAR(10));
 D  =  CAST(SUBSTR(BTS_ORDER_CHAR, 4, 4)AS INTEGER);
 D1 =  CAST(SUBSTR(BTS_ORDER_CHAR, 1, 1)AS INTEGER);
 D2 =  CAST(SUBSTR(BTS_ORDER_CHAR, 2, 2)AS INTEGER);
 D3 =  CAST(SUBSTR(BTS_ORDER_CHAR, 3, 3)AS INTEGER);
 D4 =  CAST(SUBSTR(BTS_ORDER_CHAR, 4, 4)AS INTEGER);
 D5 =  CAST(SUBSTR(BTS_ORDER_CHAR, 5, 5)AS INTEGER);
 D6 =  CAST(SUBSTR(BTS_ORDER_CHAR, 6, 6)AS INTEGER);
 D7 =  CAST(SUBSTR(BTS_ORDER_CHAR, 7, 7)AS INTEGER);
 D8 =  CAST(SUBSTR(BTS_ORDER_CHAR, 8, 8)AS INTEGER);
 D9 =  CAST(SUBSTR(BTS_ORDER_CHAR, 9, 9)AS INTEGER);
 D10 = CAST(SUBSTR(BTS_ORDER_CHAR, 10, 10)AS INTEGER);

 /* multiply the digits by its weighting factor */
 D4 = D4 * 2;
 D5 = D5 * 1;
 D6 = D6 * 2;
 D7 = D7 * 1;
 D8 = D8 * 2;
 D9 = D9 * 1;
 D10 = D10 * 2;

 /* apply trunc and mod fuctions to get right result */

  IF (D4 > 9) then
 D4 = F_Truncate(D4-MOD(D4,10)/10 + MOD(D4,10));
  IF (D5 > 9) then
 D5 = F_Truncate(D5-MOD(D5,10)/10 + MOD(D5,10));
  IF (D6 > 9) then
 D6 = F_Truncate(D6-MOD(D6,10)/10 + MOD(D6,10));
  IF (D7 > 9) then
 D7 = F_Truncate(D7-MOD(D7,10)/10 + MOD(D7,10));
  IF (D8 > 9) then
 D8 = F_Truncate(D8-MOD(D8,10)/10 + MOD(D8,10));
  IF (D9 > 9) then
 D9 = F_Truncate(D9-MOD(D9,10)/10 + MOD(D9,10));
  IF (D10 > 9) then
 D10 = F_Truncate(D10-MOD(D10,10)/10 + MOD(D10,10));

 Add_Digits = D4+D5+D6+D7+D8+D9+D10;
 Get_Digit = MOD(Add_Digits,10);
 IF (Get_Digit > 0) then
 Get_Digit = 10 - Get_Digit;
  ELSE
  IF (Get_Digit = 0) then
  Get_Digit = 0;

 C  = CAST(D  AS VARCHAR(2));
 C1 = CAST(D1 AS VARCHAR(2));
 C2 = CAST(D2 AS VARCHAR(2));
 C3 = CAST(D3 AS VARCHAR(2));
 C4 = CAST(D4 AS VARCHAR(2));
 C5 = CAST(D5 AS VARCHAR(2));
 C6 = CAST(D6 AS VARCHAR(2));
 C7 = CAST(D7 AS VARCHAR(2));
 C8 = CAST(D8 AS VARCHAR(2));
 C9 = CAST(D9 AS VARCHAR(2));
 C10 = CAST(D10 AS VARCHAR(2));
 C_DIGIT = CAST(Get_Digit AS VARCHAR(2));

 ORDER_NO = CAST(C1||C2||C3||C||C5||C6||C7||C8||C9||C10||C_Digit AS
INTEGER);
  suspend;
end
----------------------------

Quote
"Charles Urbina" <curb...@earthlink.net> wrote in message

news:3cd96ced$1_2@dnews...
Quote
> I'm executing a Sp that compiled fine but when I execute it I get this
> error.

> ISC ERROR CODE:335544321
> ISC ERROR MESSAGE:
> arithmetic exception, numeric overflow, or string truncation

> This is the offending line
> ORDER_NO = CAST(D1||D2||D3||D4||D5||D5||D6||D7||D8||D9||D10||Get_Digit AS
> INTEGER);
> below is a copy of the procedure can someone take look please.
> ---------------------- Store Procedure ------------------
> create procedure BTS
> returns (
>     ORDER_NO Integer)
> as
> Declare Variable BTS_Order Integer;
> Declare Variable BTS_Order_Char Char(10);
> Declare Variable D1 Integer;
> Declare Variable D2 Integer;
> Declare Variable D3 Integer;
> Declare Variable D4 Integer;
> Declare Variable D5 Integer;
> Declare Variable D6 Integer;
> Declare Variable D7 Integer;
> Declare Variable D8 Integer;
> Declare Variable D9 Integer;
> Declare Variable D10 Integer;
> Declare Variable Add_Digits Integer;
> Declare Variable Get_Digit Integer;

> begin
>  /* break the order number to calculate the check digit */
>  BTS_ORDER = GEN_ID(BTS_ORDER_NO, 1);
>  BTS_ORDER_CHAR = CAST(BTS_ORDER AS CHAR(10));
>  D1 =  CAST(SUBSTR(BTS_ORDER_CHAR, 1, 1)AS INTEGER);
>  D2 =  CAST(SUBSTR(BTS_ORDER_CHAR, 2, 2)AS INTEGER);
>  D3 =  CAST(SUBSTR(BTS_ORDER_CHAR, 3, 3)AS INTEGER);
>  D4 =  CAST(SUBSTR(BTS_ORDER_CHAR, 4, 4)AS INTEGER);
>  D5 =  CAST(SUBSTR(BTS_ORDER_CHAR, 5, 5)AS INTEGER);
>  D6 =  CAST(SUBSTR(BTS_ORDER_CHAR, 6, 6)AS INTEGER);
>  D7 =  CAST(SUBSTR(BTS_ORDER_CHAR, 7, 7)AS INTEGER);
>  D8 =  CAST(SUBSTR(BTS_ORDER_CHAR, 8, 8)AS INTEGER);
>  D9 =  CAST(SUBSTR(BTS_ORDER_CHAR, 9, 9)AS INTEGER);
>  D10 = CAST(SUBSTR(BTS_ORDER_CHAR, 10, 10)AS INTEGER);
>  /* multiply the digits by its weighting factor */
>  D4 = D4 * 2;
>  D5 = D5 * 1;
>  D6 = D6 * 2;
>  D7 = D7 * 1;
>  D8 = D8 * 2;
>  D9 = D9 * 1;
>  D10 = D10 * 2;

>  /* apply trunc and mod fuctions to get right result */

>   IF (D4 > 9) then
>  D4 = F_Truncate(MOD(D4,10)/10 + MOD(D4,10));
>   IF (D5 > 9) then
>  D4 = F_Truncate(MOD(D5,10)/10 + MOD(D5,10));
>   IF (D6 > 9) then
>  D4 = F_Truncate(MOD(D6,10)/10 + MOD(D6,10));
>   IF (D7 > 9) then
>  D4 = F_Truncate(MOD(D7,10)/10 + MOD(D7,10));
>   IF (D8 > 9) then
>  D4 = F_Truncate(MOD(D8,10)/10 + MOD(D8,10));
>   IF (D9 > 9) then
>  D4 = F_Truncate(MOD(D9,10)/10 + MOD(D9,10));
>   IF (D10 > 9) then
>  D4 = F_Truncate(MOD(D10,10)/10 + MOD(D10,10));

>  Add_Digits = D4+D5+D6+D7+D8+D9+D10;
>  Get_Digit = MOD(Add_Digits,10);
>  IF (Get_Digit > 0) then
>  Get_Digit = 10 - Get_Digit;
>  /*Order_No = Get_Digit;*/
>  ORDER_NO = CAST(D1||D2||D3||D4||D5||D5||D6||D7||D8||D9||D10||Get_Digit AS
> INTEGER);
>  exit;
>   /*suspend;*/
> end
> --------------------- end of Procedure ----------------------------
> Tia
> Charles Urbina

Other Threads