MVSFORUMS.com :: View topic - SQLCODE -420

through

#1: SQLCODE -420 - Please help!!! Author: vsr100481,

Posted: Mon Nov 20, 2006 1:02 pm
—
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:

Code:

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: Author: kolusu, Location: San Jose

Posted: Mon Nov 20, 2006 1:34 pm
—
vsr100481,

Do you always have numeric value in FILTER_CRIT_4 ? If so you wouldn't have any problem. If you have alphabets in the column then your comparision will fail.

you can try this

Code:

DOUBLE(FIL.FILTER_CRIT_4) > TRANS.MTM_EUR

Hope this helps....

Cheers

Kolusu

MVSFORUMS.com -> Database

output generated using printer-friendly topic mod. All times are GMT - 5 Hours

Page 1 of 1