I am getting the below error , can anyone guide to get rid of it.

Code :

JOIN CLEAR *
JOIN CARRIER AND FORM AND SERIAL IN HOLDA
TO CARRIER_NUMBER AND FORM_NUMBER AND SERIAL_NUMBER IN TPSGRTKT AS J1
END
JOIN SEQUENCE_NUMBER IN HOLDA
TO ALL FK_SEQUENCE_NUMBER IN TPAYMENT AS J2
END
JOIN FK_PTKDSGN_SEQ_NBR IN HOLDA TO SEQUENCE_NUMBER IN TPTKDSGN AS J3
END
-*-
Error :
(FOC1425) OVERFLOW OR CONVERSION ERROR ON ANSWERSET ITEM # : 20/SEQUENCE_NUMBER
(FOC1400) SQLCODE IS 304 (HEX: 00000130)
: DSNT404I SQLCODE = 304, WARNING: A VALUE WITH DATA TYPE INTEGER CANNOT
: BE ASSIGNED TO A HOST VARIABLE BECAUSE THE VALUE IS NOT WITHIN
: THE RANGE OF THE HOST VARIABLE IN POSITION 020 WITH DATA TYPE
: SMALLINT
: DSNT418I SQLSTATE = 01515 SQLSTATE RETURN CODE
: DSNT415I SQLERRP = DSNXROUA SQL PROCEDURE DETECTING ERROR
: DSNT416I SQLERRD = -120 0 0 -1 0 0 SQL DIAGNOSTIC INFORMATION
: DSNT416I SQLERRD = X'FFFFFF88' X'00000000' X'00000000'
(FOC1407) SQL FETCH CURSOR ERROR. : TPSGRTKT

This message has been edited. Last edited by: <Emily McAllister>,

I assume this is a DB2 database source.

It looks like you are retrieving an integer and trying to store it in something designed to hold only a small integer.

Too much water, not enough bucket.

What is the FORMAT of all the fields involved in the joins and what's the dbms of HOLDA, TPSGRTKT and TPAYMENT and TPTKDSGN?

Are they all in the same DBMS or are you joining a HOLD file to a DBMS file? When do you get the error? When you issue the JOIN or when you try to use the joined structure in a Chart or a Report? If the latter, provide an example of what the TABLE request looks like.

I am joining a HOLD file to a DBMS file.
I get this error when trying to use this join structure.

Please find the code as below:

JOIN CLEAR *
JOIN CARRIER AND FORM AND SERIAL IN HOLDA
TO CARRIER_NUMBER AND FORM_NUMBER AND SERIAL_NUMBER IN TPSGRTKT AS J1
END
JOIN SEQUENCE_NUMBER IN HOLDA
TO ALL FK_SEQUENCE_NUMBER IN TPAYMENT AS J2
END
JOIN FK_PTKDSGN_SEQ_NBR IN HOLDA TO SEQUENCE_NUMBER IN TPTKDSGN AS J3
END
-*-
DEFINE FILE HOLDA
ISSUE_AGENCY/A7 = EDIT(FK_TRVDSTR_ISSU_CD,'$$9999999');
SELL_AGENCY/A7 = EDIT(FK_TRVDSTR_SELL_CD,'$$9999999');
VALID_FX_TKT/A1 = IF (TICKET_DESIGNATOR LIKE 'FX%')
THEN 'Y' ELSE 'N';
END
-*-
SET ASNAMES=ON
TABLE FILE HOLDA
-*-
PRINT SELL_AGENCY OVER
FK_ACTCOMP_CD OVER
PAYMENT_SEQ_NUMBER OVER
FK_CURENCY_CONVERT OVER
CARRIER_NUMBER OVER
FORM_NUMBER OVER
SERIAL_NUMBER OVER
DATE_OF_ISSUE OVER
BOOKING_DATE OVER
AIRPORT_TKT_ORG OVER
AIRPORT_TKT_DST OVER
CITY_TKT_ORG_IATA OVER
CITY_TKT_DST_IATA OVER
TOUR_CODE OVER
PNR_LOCATOR OVER
CNV_TKT_FARE_AMT OVER
FK_MKSALSR_CD
FK_DMFAREB_SEQ_NBR
DATE_OF_ISSUE
-
BY ISSUE_AGENCY
BY FK_SEQUENCE_NUMBER
BY FOP_CONVERTED_AMT
-
IF VALID_FX_TKT EQ 'N'
ON TABLE HOLD AS HOLDB
END
Error is thrown after this PRINT .

I would check the format of FK_SEQUENCE_NUMBER

In database the FK_SEQUENCE_NUMBER has width as 14 while all other SEQUENCE_NUMBER are with width 11, so I edited and made all equal to 11. Still the error was same.

So, this is a join between two different data types. Are you creating HOLD with a format? Have you tried HOLD FORMAT FOCUS with a few INDEX fields? You may also try a single join first test a report off of it, if it works add another and test again. That might help narrow down where the problem starts.

Hi,
I tried using " ? HOLD " in between each join.
In my last join (J3) , I see that the SEQUENCE_NUMBER is having format as I6 while it should be I9.
Can u give a clue for how to edit to get the correct format match.
Below is the HOLD file master definition.

DEFINITION OF HOLD FILE: HOLDA
FIELDNAME ALIAS FORMAT

CARRIER E01 I6
FORM E02 I6
SERIAL E03 I9
TICKET_NUM E04 A14
SEQUENCE_NUMBER SEQUENCE_NU> P13.0
FK_PSGRTYP_CD FK_PSGRTYP_> A3
FK_TRVDSTR_SELL_CD FK_TRVDSTR_> I9
FK_TRVDSTR_ISSU_CD FK_TRVDSTR_> I9
FK_PRASLTY_CD FK_PRASLTY_> A1
FK_CMPRESY_CRS_CD FK_CMPRESY_> A3
FK_PTRVREG_CD FK_PTRVREG_> A4
FK_MKSALSR_CD FK_MKSALSR_> A1
FK_CURENCY_CONVERT FK_CURENCY_> A3
FK_CURENCY_TKT_RPT FK_CURENCY_> A3
FK_SPLSVTP_CD FK_SPLSVTP_> A2
FK_TKREVTY_CD FK_TKREVTY_> A1
AIRPORT_PT_INFL AIRPORT_PT_> A4
AIRPORT_AC_ORG AIRPORT_AC_> A4
AIRPORT_AC_DST AIRPORT_AC_> A4
AIRPORT_TKT_ORG AIRPORT_TKT> A4
AIRPORT_TKT_DST AIRPORT_TKT> A4
CITY_TKT_ORG_IATA CITY_TKT_OR> A4
CITY_TKT_DST_IATA CITY_TKT_DS> A4
FK_DMFAREB_SEQ_NBR FK_DMFAREB_> I9
FK_PTKDSGN_SEQ_NBR FK_PTKDSGN_> I9
CARRIER_NUMBER CARRIER_NUM> I6
FORM_NUMBER FORM_NUMBER I6
SERIAL_NUMBER SERIAL_NUMB> I9
DATE_OF_ISSUE DATE_OF_ISS> YYMD
PNR_LOCATOR PNR_LOCATOR A7
TWO_WAYS_IND TWO_WAYS_IND I6
CNV_TKT_FARE_AMT CNV_TKT_FAR> P11.2
TRANS_CNTL_NUMBER TRANS_CNTL_> A10
TICKET_AGENT_ID TICKET_AGEN> A2
TOT_RPTCOM_CNV_AMT TOT_RPTCOM_> P11.2
COMM_PERCENT COMM_PERCENT P7.2
TKT_PSGR_COUNT TKT_PSGR_CO> I6
TOUR_CODE TOUR_CODE A15
GROSS_FARE_AMT GROSS_FARE_> P11.2
NET_FARE_AMT NET_FARE_AMT P11.2
DISCOUNT_AMT DISCOUNT_AMT P11.2
SRV_CHRG_AMT SRV_CHRG_AMT P11.2
CNCL_PEN_AMT CNCL_PEN_AMT P11.2
NUMBER_OF_COUPONS NUMBER_OF_C> I6
PSGRTKT_LAST_UPDT PSGRTKT_LAS> A26
BOOKING_DATE BOOKING_DATE YYMD
FK_SEQUENCE_NUMBER FK_SEQUENCE> P13.0
FOP_SEQ_NUMBER FOP_SEQ_NUM> I6
FK_CURENCY_CONVERT FK_CURENCY_> A3
FK_ACTCOMP_CD FK_ACTCOMP_> A2
PAYMENT_SEQ_NUMBER PAYMENT_SEQ> A19
CC_TYP_CDE CC_TYP_CDE A2
FOP_CONVERTED_AMT FOP_CONVERT> P13.2
PAYMENT_LAST_UPDT PAYMENT_LAS> A26
SEQUENCE_NUMBER SEQUENCE_NU> I6
TICKET_DESIGNATOR TICKET_DESI> A14
DESCRIPTION DESCRIPTION A30

see the 3rd last field SEQUENCE_NUMBER