Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2005
    Posts
    5

    Unanswered: Compare integer values in Char (40) with a decimal field

    Hi Every one,

    Can any one help me to fine out how to compare a character field which has integer value with an Comp-2 field in SQL:

    I tried with CAST function its giving me -420 as sqlerror. So please kindly have a look at the below query and let me know the way to compare

    Example:

    EXEC SQL DECLARE V040-F7-CURSOR CURSOR WITH HOLD FOR
    SELECT
    TOP.MCC_GRK
    ,TOP.VERSION_NO
    ,TOP.VERSION_STATUS
    ,TOP.SYSTEM_ID
    ,TOP.PRODUKT_ID
    ,FIL.FILTER_EXP_CODE
    ,MAP.MAP_ATTR2
    FROM
    MC060T AKT
    ,MC030T TOP
    ,MC035T TRANS
    ,MC093T FIL
    ,MC094T MAP
    WHERE
    AKT.PRODUKT_ID = 'COMM'
    AND AKT.ST_TRADE_RELEV <> 'N'
    AND AKT.AVK_DATE = '10.11.2006'
    AND AKT.VERSION_STATUS = 2
    AND AKT.MCC_GRK = TOP.MCC_GRK
    AND AKT.VERSION_NO = TOP.VERSION_NO
    AND AKT.VERSION_STATUS = TOP.VERSION_STATUS
    AND AKT.MCC_GRK = TRANS.MCC_GRK
    AND AKT.VERSION_NO = TRANS.VERSION_NO
    AND AKT.VERSION_STATUS = TRANS.VERSION_STATUS

    AND TOP.SYSTEM_ID = FIL.SYSTEM_ID
    AND TOP.PRODUKT_ID = FIL.PRODUKT_ID

    AND FIL.ST_ACTIVE = 'Y'
    AND FIL.CLIENT_ID = ' '
    AND FIL.FILTER_NAME = 'LAST-REVAL-PV'

    AND CAST(FIL.FILTER_CRIT_4 AS INTEGER)
    > CAST(ABS(TRANS.MTM_EUR) AS INTEGER


    AND '10.11.2006' BETWEEN FIL.VALID_FROM
    AND FIL.VALID_UNTIL

    AND MAP.PRODUKT_ID = AKT.produkt_id
    AND MAP.MAP_ID = 'EXC_DESC'
    AND MAP.MAP_ATTR1 = FIL.FILTER_EXP_CODE
    AND MAP.ST_ACTIVE = 'Y'
    AND '10.11.2006' BETWEEN MAP.VALID_FROM
    AND MAP.VALID_UNTIL

    In the above query FIL.FILTER_CRIT_4 is char(40) not null. and TRANS.MTM_EUR is USAGE COMP-2.

    Note: the above query is running successfully when i run thru SPUFI. But when i run thru the program its giving me SQLCODE as -420.

    Please let me know the correct query to compare in this scenorio.

    many thanks!!!
    with regards
    Sundar

  2. #2
    Join Date
    Dec 2005
    Posts
    273
    AND CAST(FIL.FILTER_CRIT_4 AS INTEGER)
    > CAST(ABS(TRANS.MTM_EUR) AS INTEGER )

    there's a right bracket missing ...


    ensure, that there is no hostvariable named TRANS.MTM_EUR, ( DB2 might use the host-variable instead of the column name )

Posting Permissions

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