First i am creating a table using SQL code and i am inserting data into the EXCH_DETAILS table created by me.
here i am facing some problems while inserting data. Please let me know is there any solution????

SQL CREATE TABLE EXCH_DETAILS
(CURRENCY_CODE CHAR(5), YEAR INTEGER, EXCHANGE_RATE DECIMAL);
END

MODIFY FILE EXCH_DETAILS
DATA

TABLE FILE CUREX_DTL
PRINT CURRENCY_RATE
WHERE CUR_CODE EQ 'USD'
WHERE CURRENCY_YEAR EQ '2009'
ON TABLE HOLD AS CUR_RATE FORMAT ALPHA
END
-RUN
0 NUMBER OF RECORDS IN TABLE= 0 LINES= 0
(FOC209) THE DATA VALUE EXCEEDS ITS LENGTH SPECIFICATION: TABLE FILE
CUREX_DTL
(FOC310) PART OF REJECTED TRANSACTION: PRINT CURRENCY_RATE
(FOC310) PART OF REJECTED TRANSACTION: WHERE CUR_CODE EQ 'USD'
(FOC310) PART OF REJECTED TRANSACTION: WHERE CURRENCY_YEAR EQ '2009'
(FOC310) PART OF REJECTED TRANSACTION: ON TABLE HOLD AS CUR_RATE FORMAT ALPHA
0 TRANSACTIONS: TOTAL = 1 ACCEPTED= 0 REJECTED= 1
SEGMENTS: INPUT = 0 UPDATED = 0 DELETED = 0
-READ CUR_RATE &CUR_RATE.A10.
0 ERROR AT OR NEAR LINE 34 IN PROCEDURE currency_code.fex
(FOC339) DIALOGUE MANAGER -READ FAILED: CHECK FILEDEF OR ALLOCATION FOR: -READ
CUR_RATE &CUR_RATE.A10.

This message has been edited. Last edited by: Kerry,

The Original Code is here...

SQL CREATE TABLE EXCH_DETAILS
(CURRENCY_CODE CHAR(5), YEAR INTEGER, EXCHANGE_RATE DECIMAL);
END

-*
MODIFY FILE EXCH_DETAILS
DATA

TABLE FILE CUREX_DTL
PRINT CURRENCY_RATE
WHERE CUR_CODE EQ 'USD'
WHERE CURRENCY_YEAR EQ '&YY'
ON TABLE HOLD AS CUR_RATE FORMAT ALPHA
END
-RUN
-READ CUR_RATE &CUR_RATE.A10.

'USD','&YY','&CUR_RATE',$

END
-*

TABLE FILE EXCH_DETAILS
PRINT *
BY CURRENCY_CODE NOPRINT
ON TABLE PCHOLD FORMAT HTML
END

Santhu,

You want to load data into EXCH_DETAILS, extracting it from CUREX_DTL, for USD and year &YY.

For that, using the TABLE command, you create a HOLD file with 3 fields: CURRENCY_CODE, YEAR, EXCHANGE_RATE. Then, using MODIFY, you load the data into your EXCH_DETAILS table.
See below:

  
SET ASNAMES=ON
TABLE FILE CUREX_DTL
PRINT 
CUR_CODE AS 'CURRENCY_CODE'
CURRENCY_YEAR AS 'YEAR'
CURRENCY_RATE AS 'EXCHANGE_RATE'
WHERE CUR_CODE EQ 'USD'
WHERE CURRENCY_YEAR EQ '&YY'
ON TABLE HOLD AS HOLDRATE FORMAT ALPHA
END
-RUN
MODIFY FILE EXCH_DETAILS
FIXFORM FROM HOLDRATE
DATA ON HOLDRATE
END

Thanks Danny That's great...

The data appears like this
CURR_CODE YEAR EXCHANGE_RATE
USD 2009 1.00
USD 2008 1.00
USD 2007 1.00
USD 2005 1.00
USD 2004 1.00
USD 2003 1.00

we can observe that In the above code 2006 year is missing.

But my requirement is like this...
if there is no entry for a particular year we need to get the data form previous year (2005 exchange_rate and dispaly the record(data)

CURR_CODE YEAR EXCHANGE_RATE
USD 2009 1.00
USD 2008 1.00
USD 2007 1.00
USD 2006 1.00
USD 2005 1.00
USD 2004 1.00
USD 2003 1.00

This message has been edited. Last edited by: Santhu,

Santhu,
To get any missing years into the same table try this:

-* use the EXCH_DETAILS file to find out which years are missing after loading it
-* if the records are not ordered by YEAR then Table File them first into correct order
-*
SET ASNAMES=ON

DEFINE FILE EXCH_DETAILS
 NEW_YR/I4=IF YEAR NE LAST YEAR + 1 THEN LAST YEAR + 1 ELSE 1;
 NEW_CD/A5=IF NEW_YR GT 1 THEN LAST CURRENCY_CODE ELSE '';
 NEW_RT/D10.4=IF NEW_YR GT 1 THEN LAST EXCHANGE_RATE ELSE 0;
END

TABLE FILE EXCH_DETAILS
PRINT 
  NEW_CD AS 'CURRENCY_CODE'
  NEW_YR AS 'YEAR'
  NEW_RT AS 'EXCHANGE_RATE'

WHERE NEW_YR GT 1;

ON TABLE HOLD AS HOLDRATE FORMAT ALPHA
END
-RUN

MODIFY FILE EXCH_DETAILS
FIXFORM FROM HOLDRATE
DATA ON HOLDRATE
END