Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Feb 2010
    Posts
    6

    Question Unanswered: Bad Arithmetic Conversion

    I am hoping this is the correct forum for this. I am running DB2 and SQL in IMS.
    When I submit my job, I get an error: Bad Arithmetic Conversion on this line
    check = check + (substr(SUM,Ndx,1) *, wgtFactor.Ndx)

    HTML Code:
    FULL = substr(pattern,1,10) || "0000001"                     
    FULL = translate(FULL,"11","&*")                              
    SUM = translate(FULL,,                                   
    "12345678123457923456789","ABCDEFGHJKLMNPRSTUVWXYZ")                
    wgtFactor.1 = 8                                                     
    wgtFactor.2 = 7                                                     
    wgtFactor.3 = 6                                                     
    wgtFactor.4 = 5                                                     
    wgtFactor.5 = 4                                                     
    wgtFactor.6 = 3                                                     
    wgtFactor.7 = 2                                                     
    wgtFactor.8 = 10                                                    
    wgtFactor.9 = 0                                                     
    wgtFactor.10 = 9                                                    
    wgtFactor.11 = 8                                                    
    wgtFactor.12 = 7                                                    
    wgtFactor.13 = 6                                                    
    wgtFactor.14 = 5                                                    
    wgtFactor.15 = 4                                                    
    wgtFactor.16 = 3                                                    
    wgtFactor.17 = 2                                                    
    check = 0                                                        
    do Ndx = 1 to 17                                                 
        check = check + (substr(SUM,Ndx,1) *,   wgtFactor.Ndx)                                               
    end                                                                 
    Digit = check // 11                                         
    if Digit = 10 then                                             
        Digit = "X"                                                
    FULL = substr(FULL,1,8) || Digit || substr(FULL,10,8) 

  2. #2
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    translate(FULL,,
    "12345678123457923456789","ABCDEFGHJKLMNPRSTUVWXYZ ")

    12345678123457923456789
    ABCDEFGHJKLMNPRSTUVWXYZ

    Has to be same number of characters. If not extra characters translated to spaces, which is not the digits.

    Lenny

  3. #3
    Join Date
    Feb 2010
    Posts
    6
    translate(FULL,, "12345678123457923456789","ABCDEFGHJKLMNPRSTUVWXYZ ")


    Both have 23 characters.

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It looks like you have unnecessary commas all over the place. Not to mention the fact that you shouldn't multiply integers by strings.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by n_i View Post
    It looks like you have unnecessary commas all over the place. Not to mention the fact that you shouldn't multiply integers by strings.
    There are not a DB2 code (I think).
    But translate command working for any languages in the same way...

    Lenny

  6. #6
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by rcanter View Post
    translate(FULL,, "12345678123457923456789","ABCDEFGHJKLMNPRSTUVWXYZ ")


    Both have 23 characters.
    Extra comma, anyway...

  7. #7
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Lightbulb

    You lost the letter "I" (for examle), also you did non convert the special characters and lower case letters, so if you will try to do following:

    Code:
    select translate('IIIIIIabcd',                                   
    '12345678123457923456789','ABCDEFGHJKLMNPRSTUVWXYZ')  "Translated"
    from sysibm.sysdummy1;
    you'll get the result:

    Translated
    IIIIIIabcd
    So if in pattern you have "I", or lower case letters you'll have an Arithmetic exception....

    Lenny
    Last edited by Lenny77; 02-24-10 at 17:10.

  8. #8
    Join Date
    Feb 2010
    Posts
    6
    I have tried removing the comma and still getting same error. Besides, the comma is telling it to continue on the next line (statement not finished).

    There are specific letters missing for a reason. And lowercase doesn't concern me. Both of those are convered in other storage procedures that this job calls.
    Last edited by rcanter; 02-24-10 at 17:48.

  9. #9
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963

    Question

    Quote Originally Posted by rcanter View Post
    I have tried removing the comma and still getting same error. Besides, the comma is telling it to continue on the next line (statement not finished).

    There are specific letters missing for a reason. And lowercase doesn't concern me. Both of those are convered in other storage procedures that this job calls.
    Code:
    check = check + (substr(SUM,Ndx,1) *,   wgtFactor.Ndx)
    What for this comma ?

  10. #10
    Join Date
    Feb 2010
    Posts
    6
    the wgtFactor.Ndx) is on the next line. The , specifies that.

  11. #11
    Join Date
    Jul 2009
    Location
    NY
    Posts
    963
    Quote Originally Posted by rcanter View Post
    the wgtFactor.Ndx) is on the next line. The , specifies that.
    This is really not good:
    Code:
    Digit = "X"

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    1) What is the contents of pattern?
    Are there any character other than alphanumeric or '&*' in pattern?
    What is the result of "say pattern"?
    (I guessed you are using REXX.)

    2) Is the comma not extra?
    Code:
        check = check + (substr(SUM,Ndx,1) *,   wgtFactor.Ndx)
    If the line was splitted into two lines, it would be correct.
    Code:
        check = check + (substr(SUM,Ndx,1) *,
                         wgtFactor.Ndx)

  13. #13
    Join Date
    Feb 2010
    Posts
    6
    The contents of pattern will be a number, letter or &. There are a few exceptions that will not be used, such as I, O and Q.
    The line is split with the comma being at the end the way you have it in second set of code.

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    The contents of pattern will be a number, letter or &.
    How did you confirmed that?
    (If you convinced that from your program logic/code, it may include bugs.)
    What are the result of "say pattern" and "say FULL"?
    Don't these include blanks?

  15. #15
    Join Date
    Feb 2010
    Posts
    6
    No blanks. The data is drawn from a database that I upload. Pattern has the first 10 char's of full, which has 17.
    I think I am gonna try to display my results somehow to see the data.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •