Results 1 to 5 of 5
  1. #1
    Join Date
    May 2013
    Posts
    6

    Post Unanswered: THE VALUE IS NOT COMPATIBLE WITH THE DATA TYPE OF ITS TARGET Error

    Hi,

    I am new to DB2. i'm in the process of creatind DB2 proc. Below is my Sample Proc code,

    Table Structure:
    CREATE TABLE GSU.TRADES_EXEC (
    ACCOUNT_NO VARCHAR(20) FOR SBCS DATA NOT NULL WITH DEFAULT,
    TOTAL_AMT INTEGER NOT NULL WITH DEFAULT);

    CODE:
    CREATE PROCEDURE P_CLR_TEST ()
    BEGIN
    DECLARE varSql_Query CLOB;
    SET varSql_Query = ' Insert into gsu.TRADES_EXEC
    (ACCOUNT_NO,
    TOTAL_AMT)
    Select tab1.Acct_Nm, 15000
    From Trades tab1' ;

    execute immediate varSql_Query;
    END


    While running the Above sample proc i'm getting below error message,

    ERROR:
    THE VALUE IS NOT COMPATIBLE WITH THE DATA TYPE OF ITS TARGET. TARGET NAME IS TOTAL_AMT. SQLCODE=-408, SQLSTATE=42821, DRIVER=4.13.111
    Please help me to solve this issue.

    Thanks in Advance.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    working for me
    (0)[db2inst1@dlx00031 work]$ db2 "call P_CLR_TEST()"

    Return Status = 0
    (0)[db2inst1@dlx00031 work]$ db2 "select * from gsu.TRADES_EXEC"

    ACCOUNT_NO TOTAL_AMT
    -------------------- -----------
    aa 15000
    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
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    have you added
    END
    @
    into proc and created with -td@ ??
    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

  4. #4
    Join Date
    May 2013
    Posts
    6
    thanks for ur reply....But i'm running this proc from DataStudio3.1..still i get this error i dont know wat mistake i have done. Can you please review the below proc that i have created,

    CODE:
    CREATE PROCEDURE P_CLR_TEST (IN the_run_id DECIMAL(19))
    BEGIN
    DECLARE conProcedureName varchar(29);
    DECLARE varSql_Query CLOB;
    DECLARE varReportName varchar(20);
    DECLARE varRunId DECIMAL(19);

    SET varReportName = 'Trades Received';

    IF varReportName = 'Trades Received' THEN
    SET varRunId = the_run_id;
    SET conProcedureName = 'P_CLR_TEST';

    SET varSql_Query = ' Insert into gsu.TRADES_EXEC
    (TEX_ACCOUNT_NO,
    TEX_ACCOUNT_NM,
    TEX_ACCOUNT_SETTLEMENT_DATE,
    TEX_BASE_CURRENCY,
    TEX_CLIENT_REF_ID,
    TEX_CONTRACTUAL_SETTLEMENT_DT,
    TEX_CPTY_NM,
    TEX_CPTY_AGENT,
    TEX_CPTY_ACCOUNT_NM,
    TEX_CPTY_AGENT_BIC_CD,
    --10
    TEX_CPTY_BIC_CD,
    TEX_COUNTRY_OF_ORIGIN_DE,
    TEX_CUSIP,
    TEX_RECEIVED_DT,
    TEX_DEPOT,
    TEX_TOTAL_BASE_AM,
    TEX_TOTAL_PRICE_AM,
    TEX_TOTAL_LOCAL_AM,
    TEX_OUTSTANDG_LOCAL_AM,
    TEX_PARTIAL_SETTLE_LOCAL_AM,
    --10
    TEX_ISIN_NO,
    TEX_LOCAL_CCY_CD,
    TEX_MARKET_DE,
    TEX_MESSAGE_ID,
    TEX_NOMINAL_QY,
    TEX_OPERATOR_NAME,
    TEX_OUTSTANDG_BASE_AM,
    TEX_OUTSTANDG_CONSIDERATION_AM,
    TEX_OUTSTANDG_NOMINAL_QY,
    TEX_PARTIAL_SETTLE_CASH,
    --10
    TEX_PARTIAL_SETTLE_TODAY,
    TEX_REASON,
    TEX_SECURITY_CLASS_DE,
    TEX_SEDOL,
    TEX_SECURITY_DESC,
    TEX_SETTLEMENT_CURRENCY_AM,
    TEX_SPECIAL_INSTRUCTION_TX,
    TEX_cd_STATUS,
    TEX_STATUS_NARRATIVE,
    TEX_TOTAL_CONSIDERATION,
    --10
    TEX_TRADE_DATE,
    TEX_TRADE_TYPE_CD,
    TEX_TRADE_TYPE_DESC,
    TEX_TRANSACTION_REFERENCE_TX,
    TEX_MARKET_STATUS,
    TEX_TRADE_CODES,
    TEX_AGE_OF_FAIL,
    TEX_PARTIAL_SETTLE_BASE_AM,
    TEX_EXCHANGE_RATE,
    TEX_STATUS_CD,
    --10
    TEX_NO_TRADE_REMARKS,
    TEX_DEPOSITORY,
    TEX_EXCHANGE_INDICATOR,
    TEX_COUNTERPARTY_AGENT_CD,
    TEX_COUNTERPARTY_CD,
    TEX_DEPOT_CD,
    TEX_MARKET_CD,
    TEX_MARK_STATUS,
    TEX_REPORT_EXECUTE_REQUEST_ID,
    TEX_CREATE_UI,
    --10
    TEX_CREATE_TS,
    TEX_LAST_UPDATE_TS,
    TEX_LAST_UPDATE_UI
    )
    Select CNT.CNT_CLNM, -- ACCOUNT_NM
    '''' , -- ACCOUNT_SETTLEMENT_DATE
    ''USD'', -- BASE_CURRENCY
    CNT.CNT_CUSREF, --CLIENT_REF_ID
    CNT.CNT_SETDTE, -- CONTRACTUAL_SETTLEMENT_DT
    ''BROKER_TEST'', -- CPTY_NM
    ''BROKER_AGENT_TEST'', -- CPTY_AGENT
    '''', --CPTY_ACCOUNT_NM -- Counter Agent Account
    '''', -- CPTY_AGENT_BIC_CD
    -- 10
    '''', --BRK.CCMS_EXTRNL_REF_ID, -- CPTY_BIC_CD
    '''', --CRCL.COUNTRY_OF_QUOT_L_DE,
    --cdl.LONG_DE, -- COUNTRY_OF_ORIGIN_DE
    CNT.CNT_CUSIP, -- CUSIP
    CNT.CNT_DATRCV, -- RECEIVED_DT
    '''', -- DEPOT
    '''', -- TOTAL_BASE_AM --indicative base price
    '''', -- TOTAL_PRICE_AM -indicative local price
    '''', -- TOTAL_LOCAL_AM --indicative local value
    '''', -- OUTSTANDING_LOCAL_AM --indicative o/s local
    '''',
    -- PARTIAL_SETTLE_LOCAL_AM --indicative partial settle local
    -- 10
    CNT.CNT_ISIN, -- ISIN_NO
    '''', -- LOCAL_CCY_CD
    '''', -- MARKET_DE
    CNT.CNT_TRDEID, --MESSAGE_ID
    CNT.CNT_NOMQTY, -- NOMINAL_QY
    CNT.CNT_OPERNM, --OPERATOR_NAME
    '''', -- OUTSTANDING_BASE_AM --indicative o/s base
    CNT.CNT_VLOCSH, -- OUTSTANDING_CONSIDERATION_AM
    CNT.CNT_VLOSTK, -- OUTSTANDING_NOMINAL_QY
    '''', -- PARTIAL_SETTLE_CASH
    -- 10
    '''', -- PARTIAL_SETTLE_TODAY
    '''', -- REASON
    '''', -- SECURITY_CLASS_DE
    CNT.CNT_SEDOL, -- SEDOL
    '''', -- SECURITY_DESC
    CNT.CNT_STBCUR, -- SETTLEMENT_CURRENCY_AM
    '''', -- SPECIAL_INSTRUCTION_TX
    '''', --
    '''', -- STATUS_NARRATIVE
    10 , -- CNT.CNT_CTOTAL, -- TOTAL_CONSIDERATION
    -- 10
    CNT.CNT_TRDDTE, -- TRADE_DATE
    CNT.CNT_BARGTP, --TRADE_TYPE_CD
    '''', -- TRADE_TYPE_DESC
    CNT.CNT_SCRNO, --TRANSACTION_REFERENCE_TX
    '''', -- MARKET_STATUS
    '''', -- TRADE_CODES
    '''', -- AGE_OF_FAIL
    '''',
    -- PARTIAL_SETTLE_BASE_AM --indicative partial settle base
    '''', -- EXCHANGE_RATE
    '''',
    -- 10
    '''', -- NO_TRADE_REMARKS
    CNT.CNT_DEPOSITORY, -- DEPOSITORY
    '''', -- EXCHANGE_INDICATOR
    CNT.CNT_AGTSEC,
    CNT.CNT_BROKER,
    CNT.CNT_DEPOT,
    CNT.CNT_MARKET,
    CNT.CNT_STATUS_INVFUND, -- MARK_STATUS
    ''' || varRunId || ''',
    ''' || conProcedureName || ''',
    sysdate,
    sysdate,
    ''' || varReportName || '''
    From gsu.CCMS_NET_TRADES CNT
    WHERE CNT.CNT_IDENTIFIER = 7423760503 ' ;
    END IF;
    IF varReportName = 'Trades Received' THEN
    execute immediate varSql_Query;
    END IF;
    END

  5. #5
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    as indicated before : have you specified a different delimiter from ; (default) in setting in ds
    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
  •