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

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 )