RNF7595: The result data structure does not include a subfield in position 1

[zs6jce]

Hello,

Quick Description of what I want to happen:
I want to write a pgm in which user give me a Date and a Currency. I then look up the Date in my DB, and return the FX Rate to the user.

How I would have like to sove the problem:

            QryCur = 'EUR';
            QryDate = %Date();  //Any date, used 2011-06-18 for example
            QryAmt  = $1,000.00 


            Chain QryDate XFXRATES;  //Random Read of my XFXRATES database

#####################################################################
//The Database Fields for USD FX Rates

Date          || EUR    || GBP
2011-06-18 || 0.9898 || 0.9999

#####################################################################

          //Now I want to convert my USD amount into Euro's
          EuroAmt = QryAmt * Rate;

        //My Problem is to get Rate = 0.9898
        //As this "should" happen Rate = Eur
        //But QryCur = 'EUR'  which is a literal; so I can't just go Rate = (QryCur);
        //I believe that I have to perform a %Lookup with DataStructures and Arrays.(see below)

This is how I AM solving the problem:

My DateBase (PF) - XFXRATES.PF

     A                                      UNIQUE
     A          R FXRATESREC
     A            CURDATE         L         TEXT('Date of the Currency Row')
     A            GBP            9P 4       TEXT('Pound sterling')
     A            USD            9P 4       TEXT('US dollar')
     A**************************************************************************
     A          K CURDATE

My Program (RPGLE) - XFXRPGLE

     FXFXRATES  IF   E           K DISK


        //Currency DataStructure with overlayed Array
     D CurListDS       DS
     D                                3A   Inz('GBP')
     D                                3A   Inz('USD')
     D  CurListArray                  3A   Dim(2) OVERLAY(CurListDS)

     D CurRateDS       DS
     D  CurRateArray                  9P 4 Dim(2)


        //------------------------------------------------------Global Variables
     D I               S              2P 0
     D QRYCUR          S              3A   Inz('USD')
     D QRYDATE         S               D   Inz(*Sys)
     D BASECUR         S              3A   Inz('EUR')
     D BASEAMNT        S             10P 0 Inz(*Zeros)
     D RATE            S              7P 4 Inz(*Zeros)
     D RESULTAMNT      S             10P 0 Inz(*Zeros)

       //***********************************************************Main Program
      /Free

             Chain QryDate XFXRATES CurRateDS;

               //Valid Date?
              IF %Found(XFXRATES);
                //Valid Currency Symbol Selected?
                I = %Lookup(QRYCUR:CurListArray);
                IF I > 0;
                  RATE = CurRateArray(I);
                  RESULTAMNT  = RATE * BASEAMNT;
                ENDIF;
              ENDIF;

       *Inlr = *On;
       Return;

      /End-Free

My problem is that when I try to compile the program is get the following error:
RNF7595 The result data structure CURRATEDS does not include a subfield in position 1 for record format FXRATESREC.

Please be so kind as to advise me how to solve the RNF7595 problem as I could like to increase the number of currencies from 2 to 34.

Regards,
Christoff Erasmus

[Mercury]

Re: RNF7595: The result data structure does not include a subfield in position 1

The XFXRATES file format is not created correctly. You'd be better off if the XFXRATES file defines the record format as currency date, currency symbol, currency rate (i.e. one rate record by curdate-cursymbol) then use a compound key on curdate-cursymbol to straightforward the process

     A                                      UNIQUE
     A          R FXRATESREC
     A            CURDATE          L          
     A            CURSYMBOL       3A         
     A            CURRATE         9P 4        
     A          K CURDATE
     A          K CURSYMBOL 

             Chain (QryDate : QrySymb)  XFXRATES;
              IF %Found(XFXRATES);
                  RESULTAMNT  = BASEAMNT * CURRATE; 

If you don't change the record format you don't need any array since you already know where the searched currency is set on the record. Using this pretty awkward method, you(ll have to populate the rate currencies in the same order on each record. For example the USD rate has to necessarily be the second one in all the file records.

     A                                      UNIQUE
     A          R FXRATESREC
     A            CURDATE         L         TEXT('Date of the Currency Row')
     A            GBP            9P 4       TEXT('Pound sterling')
     A            USD            9P 4       TEXT('US dollar')
     A**************************************************************************
     A          K CURDATE 

             Chain QryDate XFXRATES;
              IF %Found(XFXRATES);
                  RESULTAMNT  = BASEAMNT * USD; 

[MichaelCatalani]

Re: RNF7595: The result data structure does not include a subfield in position 1

In order to use a resulting data structure on the chain opcode, create the data structure using the extfile or likerec keyword.

 d XfxRatesDS     ds                  LikeRec( [FONT=Courier New][SIZE=3][COLOR=#0000bb]FXRATESREC[/COLOR][/SIZE][/FONT] : *input )

Now you will be able to use the data structure without getting a compile error. This will build a qualified data structure called XFXRATESDS, that will have a structure identical to your file layout. This was the reason for you compiler error. Your resulting data structure was not built correctly to receive the data from the file.



For what you are trying to do (as Phillipe alluded to) the structure of your file is wrong. You dont want to use a single record in a file to attempt to handle EVERY currency conversion, as you will continuously have to modify the record layout if you want to add new currencies in the future. Instead, use a single record for each currency conversion. For example, your file layout should be something like this:

DATE | Convert From Currency Code | Convert To Currency Code | Conversion Multiplier

So if you want to convert the Euro to USD, you would chain to the file like this:

chain ( Date : 'EUR' : 'USD' ) XFXATES;
 
ToCurrencyCodeAmount = FromCurrencyCodeAmount * ConversionMultiplier;

If you wanted to start tracking conversion rates for new currencies in the future, all you would have to do is to populate the existing file with the currency codes and the conversion rates.

[zs6jce]

Re: RNF7595: The result data structure does not include a subfield in position 1

To Both Philippe and Michael. Thank you very much for firstly solving my problem, and then secondly teaching me that the database was designed incorrectly.

I think Philppe's idea of storing Date and Currency as a "composite" key is exactly what I needed, and makes the program much smaller, simpler and easier to understand.

To Michael, thanks for explaining that the DS and the Record must "macth" for the Chain to work. I tried to get the two to match, but had a problem with the array that I was trying to overlay.

Also Michael, I like your idea of "DATE | Convert From Currency Code | Convert To Currency Code | Conversion Multiplier", as I then need not have multiple databases to cover all the currencies.

Thank you very much for assisting me.
Christoff Erasmus

[gcraill]

Re: RNF7595: The result data structure does not include a subfield in position 1

You know I like Christoff's attitude, that was verra good manners!