Midrange News for the IBM i Community

Posted by: rpgprogrammer
Vancouver, BC
Distinct Records
has no ratings.
Published: 10 Apr 2012
Revised: 23 Jan 2013 - 1553 days ago
Last viewed on: 24 Apr 2017 (2957 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Distinct Records Published by: rpgprogrammer on 10 Apr 2012 view comments(3)



Is there  any way to store distinct records in a field or array?

For example: Below query will only store 1 record but if we execute the below query through SQL there are around 10 records.

C/Exec SQL                 
C+   Select DISTINCT Field1
C+   Into :W0TEST
C+   From FILE1          



Return to midrangenews.com home page.
Sort Ascend | Descend


(Sign in to Post a Comment)
Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Distinct Records
Posted: 5 years 15 days 3 hours 10 minutes ago

You mean something like:


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

d Array           s                   like(Field1) dim(MaxElem)

    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;

 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.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Distinct Records
Posted: 5 years 15 days 2 hours 33 minutes ago

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

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.

Posted by: rpgprogrammer
Premium member *
Vancouver, BC
Comment on: Distinct Records
Posted: 5 years 15 days 1 hours 32 minutes ago

Thanks,  This works..