d fileds          ds                  likerec(filerec)
d arrVals         s                   like(fld001)
d                                     dim(536)
d                                     based(ptrVals)
d ptrVals         s               *   inz(%addr(fileds.fld001))

The above defines a DS like your file record format.  Then creates an array of 536 elements with attributes like fld001 (assume your first numeric field in the file), but we are not reserving memory space for this array we'll place it at the address contained in ptrVals.  ptrVals is initialised to the address for the first numeric field in the data structure.  Now the array will overlay the 536 file fields and you can reference then either by the fileds.[fieldname] OR by the array element name.  To load the ds just: 

chain (keyfld1:keyfld2) file fileds;

 and of course you can change the chain to read, reade, etc

Neil's solution is a good one, but assumes the 536 amount fields are contiguous in the file.

If not contiguous, is there a pattern? For example, if it's key001, amt001, key002, amt002, etc., you can modify Neil's definition:

     Dfileds           DS                  LIKEREC(filerec)
     DarrVals          DS                  DIM(536)
     D                                     BASED(ptrVals)
     D key                                 LIKE(fileds.key001)
     D amt                                 LIKE(fileds.fld001)
     DptrVals          S               *   INZ(%ADDR(fileds.key001))

If the fields are not contiguous, and there's no pattern, I'm afraid you will need to name them, and overlay with an array. Perhaps a /COPY member?

     Damtfields        DS
     D fld001
     D fld002
     D* ...
     D fld536
     D arrVals                             LIKE(fld001)
     D                                     DIM(536)
     D                                     OVERLAY(amtfields:1)

This works. Thanks a million.

Harold

.....D myArr        DS
     D arrVals                             LIKE(fld001)
     D                                     DIM(536)

     D fldCnt       S            3S 0
      /free
        mySQL = 'SELECT ';
        for i = 1 to %elems(arrVals);
           fldCnt = i;
           mySql += 'FLD' + %editc(fldcnt:'X');
           if (i < %elems(arrVals)); 
             mySQL += ',';
           endif;
       endfor;
       mySQL = ' FROM MYFILE';
 
        // Do the declare cursor, open, thingy here...
       exec sql FETCH csr INTO :myArr;

I wrote this in this Comment, so I have (A) never tried it before, and (B) never testing it to see if its even valid... just a random thought.

Great idea. This is also off the cuff, not tested, but around line 16 I think you'll need:

       Exec SQL PREPARE stm FROM :mySQL;
       Exec SQL DECLARE csr CURSOR FOR stm;
       Exec SQL OPEN csr;

And, eventually, a CLOSE csr.

I believe I did something like that at my former employers.  But they really hated dynamic SQL for frequent run or interactive stuff - it used to hog resources building the access each time, and they had a seriously in need of rewrite order entry process that was a system hog also - that 90% of the system users were using at any given time.

While I like those neat dynamic sql functions - there are limits to their usability on systems that are creaking at the seams.

Neil,

I've switched to nearly always using dynamic for Inquiry programs (subfile or web page lists) results. I do NOT notice a bit of difference at v5r4 and later.

I agree it's generally not too big a deal - where it all breaks down is when your production box is running at 99.9% capacity with poorly written 20 year old RPG programs, being used by the vast majority of your users.  It's just enough to push things over the edge where it is noticable.

I could write dynamic sql and have no problems on development or warehousing box.  But put it live on the order entry machine and everything just died.  Then again if the buyers were working against a production deadline the entire systen would slow to a snails pace as they were all bashing the keyboards at a fevered pace.