Results 1 to 6 of 6

Thread: sql error -420

  1. #1
    Join Date
    Jun 2013
    Posts
    6

    Unanswered: sql error -420

    Hi,

    I am trying to fetch data from A.COL2 of TABLE1 A with the below sql but i am getting sql error -420.

    DSNT408I SQLCODE = -420, ERROR: THE VALUE OF A STRING ARGUMENT WAS NOT
    ACCEPTABLE TO THE DECIMAL FUNCTION
    DSNT418I SQLSTATE = 22018 SQLSTATE RETURN CODE
    DSNT415I SQLERRP = DSNXRDEC SQL PROCEDURE DETECTING ERROR
    DSNT416I SQLERRD = -2003 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
    DSNT416I SQLERRD = X'FFFFF82D' X'00000000' X'00000000' X'FFFFFFFF'
    X'00000000' X'00000000' SQL DIAGNOSTIC INFORMATION

    I tried NUMERIC,BIGINT in place of decimal but its not working.
    FYI -this sql works in development but not in production

    kindly advise

    COL2 of TABLE1 A is CHAR (20)
    COL1 OF TABLE2 is DECIMAL (10,0)

    SELECT
    DISTINCT CAST(SUBSTR(A.COL2, 1, 10) AS DECIMAL(10))
    FROM
    TABLE1 A
    WHERE
    A.COL2 <> ' '
    AND A.COL2 <> 'SYSTEM'
    AND UCASE(A.COL2 ) = LCASE(A.COL2)
    AND (
    CAST(SUBSTR(A.COL2 , 1, 10) AS DECIMAL(10))
    BETWEEN 1 AND 9999999
    OR CAST(SUBSTR(A.COL2 , 1, 10) AS DECIMAL(10))
    BETWEEN 100000000 AND 999999999)
    AND NOT EXISTS
    (SELECT COL1 FROM TABLE2
    WHERE COL1 = CAST(SUBSTR(A.COL2, 1, 10) AS DECIMAL(10)));

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    you have some 'bad' data in that column - non-numeric.
    you can either do some data cleaning, or you can change your query to handle the bad data (e.g remove/replace non-numeric characters before casting to decimal), or filter out the bad data.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    you can ..., or filter out the bad data.
    This may be not perfect, but may be worth to try...
    Code:
    ...
     FROM  (SELECT COL1 , COL2
             FROM  TABLE1
             WHERE
                   A.COL2 <> ' '
               AND A.COL2 <> 'SYSTEM' /* Is this neccesary? */
               AND UCASE(A.COL2 ) = LCASE(A.COL2)
           ) AS A
     WHERE
       (   CAST(SUBSTR(A.COL2 , 1, 10) AS DECIMAL(10))
           BETWEEN 1 AND 9999999
       OR ...
    ...

  4. #4
    Join Date
    Nov 2004
    Posts
    67
    Doesn't this part

    ...
    AND UCASE(A.COL2 ) = LCASE(A.COL2)
    ...

    give FALSE?

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    If an alphabet characer was included,
    UCASE(A.COL2 ) <> LCASE(A.COL2)

    If all characers were numeric,
    UCASE(A.COL2 ) = LCASE(A.COL2)

  6. #6
    Join Date
    Nov 2004
    Posts
    67
    Thank you, tonkuma. I thought only of alphabetic literals.

Tags for this Thread

Posting Permissions

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