Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Unanswered: Invalid character found in a character string argument

    Am getting this error while trying to insert but just selection is done i dont get any error.kindly help

    Error code:
    During SQL processing it returned:
    SQL0420N Invalid character found in a character string argument of the
    function "INTEGER". SQLSTATE=22018


    Insert Query:
    INSERT INTO ADW.ECOM_SES_REFL_W
    (CKIE_SEQ_I,
    LNK_I,
    REFL_ECOM_BROW_KYWD_I,
    GST_ENTR_ECOM_BROW_KYWD_I,
    REFL_SRC_N,
    REFL_KYWD_REF_I,
    SGL_WBPGE_VIST_F ,
    REFL_PROF_I,
    UPDT_TS)
    SELECT CKIE.CKIE_SEQ_I AS CKIE_SEQ_I,
    ECOM.LID AS LNK_I,
    KYWDE.ECOM_BROW_KYWD_I AS REFL_ECOM_BROW_KYWD_I,
    KYWDS.ECOM_BROW_KYWD_I AS GST_ENTR_ECOM_BROW_KYWD_I,
    ECOM.AFID AS REFL_SRC_N,
    ( CASE
    WHEN Instr(ECOM.LID, 'p') = 0 OR TRIM(Translate(Substr(ECOM.LID, Instr(ECOM.LID, 'p') + 1),
    ' ','+-E.0123456789')) > ' ' OR ECOM.LID = '' THEN -1
    ELSE Substr(ECOM.LID, Instr(ECOM.LID, 'p') + 1)
    END ) AS REFL_KYWD_REF_I,
    ECOM.BOUNCEACTIVITYFLAG AS SGL_WBPGE_VIST_F,
    ( CASE
    WHEN Instr(ECOM.LID, 'p') = 0
    OR TRIM(Translate(LEFT(Substr(ECOM.LID, 1, Instr(ECOM.LID, 'p')
    ),
    LENGTH(Substr(ECOM.LID, 1,Instr(ECOM.LID, 'p')))- 1),
    ' ', '+-E.0123456789')) > ' ' OR ECOM.LID = '' THEN NULL
    ELSE LEFT(Substr(ECOM.LID, 1, Instr(ECOM.LID, 'p')), LENGTH(
    Substr(ECOM.LID, 1, Instr(ECOM.LID, 'p'))) - 1) END ) AS REFL_PROF_I,
    CURRENT TIMESTAMP AS UPDT_TS
    FROM ADW.EXTL_BROW_REFL_E ECOM
    INNER JOIN ADW.ECOM_CKIE_SRC_KEY_LKUP CKIE
    ON ECOM.TRACKINGIDSESSION = CKIE.TRAK_ID_SES_I
    INNER JOIN ADW.ECOM_KYWD KYWDE
    ON ECOM.LNM = KYWDE.KYWD_T
    INNER JOIN ADW.ECOM_KYWD KYWDS
    ON ECOM.SEARCHPHRASE = KYWDS.KYWD_T
    WITH UR

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as the message indicates
    SQL0420N Invalid character found in a character string argument of the
    function "INTEGER". SQLSTATE=22018
    and according the doc
    The function function-name has a character string argument that contains a character that is not valid in a numeric SQL constant. The function may have been called as a result of using the CAST specification with function-name as the target data type. The function or data type used in the SQL statement may be a synonym for function-name.
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Sep 2011
    Posts
    3
    y am i getting this error even though am not doing any casting here!!.

    how would i find if any character is going to integer columns.

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    check that datatype of selected to insert data has the same datatype as target column
    there is something called : pd/psi problem determination problem source identification

    try to replace the select by export - insert by import
    set commitcount to 1 and try to find the row
    hopefully the error is in each record or near the start - otherwise it can take some time
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

Posting Permissions

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