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 - 1729 days ago
Last viewed on: 17 Oct 2017 (3278 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)

Hi,

 

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          
C/End-Exec                 

 

Thanks

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

COMMENTS

(Sign in to Post a Comment)
Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Distinct Records
Posted: 5 years 6 months 9 days 23 hours 15 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)

 /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.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Distinct Records
Posted: 5 years 6 months 9 days 22 hours 39 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
     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.

Posted by: rpgprogrammer
Premium member *
Vancouver, BC
Comment on: Distinct Records
Posted: 5 years 6 months 9 days 21 hours 37 minutes ago

Thanks,  This works..