You mean something like:

 *  Array limits                                  
d MaxElem         c                   99        
d MaxRows         s             10i 0 inz(MaxElem)

d Array           s                   like(Field1) dim(MaxElem)

 /free
    exec sql declare LoadArray for
             select  Distinct Field1
             from    File1;

    exec sql open LoadArray;

    exec sql fetch from LoadArray
             for   :MaxRows rows
             into  :Array;

   //  Check sql results
    NumberRecords = sqler3;

    exec sql close LoadArray;
 /end-free

 I've not tested the above as written, but I use that structure to load arrays typically I add an Order By to the Declare, and the receiver Array is usually a data structure.  I use this construct to load a lookup table of things like valid codes (tax codes, reason codes, status codes) so that I don't go looking on the CodesFile for each loop of a few hundred thousand record update batch program.

If you only want 1 row, you can limit the query directly:

     C/Exec SQL                 
     C+   Select DISTINCT Field1
     C+   Into :W0TEST
     C+   From FILE1
     C+   FETCH FIRST ROW ONLY
     C/End-Exec

See fetch-first-clause in SQL Reference chapter on Queries. fetch-first-clause limits the number of rows made available from the query. If the query without the fetch-first-clause returns 10, 100, or 5 million rows, FETCH FIRST ROW ONLY will only ever return 1 row. Since you know there is more than one row in your result table, you might also consider adding an ORDER BY to control which row you actually get.

If you decide to go with the cursor and fetch, it's perfectly fine to do a single FETCH into a standalone field (or fields); this would only return one row.

The FETCH ... FOR <so many> ROWS that Neil showed is a bulk move, so should have better performance than doing multiple fetches into non-array fields. Imagine if your result set has 1000 rows. A FETCH to a stand-alone field would have to be performed 1000 times to get all the data, but a FETCH FOR 10 ROWS into an array would only have to be performed 100 times.

Thanks,  This works..