Midrange News for the IBM i Community


Posted by: Viking
Information Systems
CA
Select distinct into array?
has no ratings.
Published: 20 Nov 2012
Revised: 02 May 2013 - 1517 days ago
Last viewed on: 27 Jun 2017 (4302 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.

Select distinct into array? Published by: Viking on 20 Nov 2012 view comments(7)

I would like to get the unique values from a file field into an array the easiest way possible...

If I want to see the unique (each one only once) locations that are in a transaction file where the location can exist many times, I can do an SQL like this:

Select distinct location from myfile

From within an RPG, how can I get that resulting list of locations from the SQL and into an array?

Thanks.

(I'll do it the old-fashioned way in the meantime until someone suggests a niftier way to do this)

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

COMMENTS

(Sign in to Post a Comment)
Posted by: Ringer
Premium member *
Comment on: Select distinct into array?
Posted: 4 years 7 months 6 days 14 hours 33 minutes ago

Execute the Select and do FETCH in a loop?

Chris Ringer

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Select distinct into array?
Posted: 4 years 7 months 6 days 13 hours 42 minutes ago

 How to load an array using SQL. Note that you need both a constant and a variable for the elements/rows value (constant for DIM, and variable for SQL statements). I've set the array element count high, but that is example only you can reduce this by just changing the constant once. You can change the sql declare statement to be whatever you need it to be.

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

d ldMiscChg       s               n                                  
d cntMiscChg      s             10i 0                                
d arrMiscChg      ds                  likeds(tmpMiscChg) dim(MaxElem)

 *************************************************************
 *  LoadMiscChgArray (local)                                  
 *************************************************************
p LoadMiscChgArray...                                         
p                 b                                           
d                 pi                                          
 /free                                                        
    exec sql declare  MiscChg cursor for                       
             select   [list fields]
             from     MiscChgF                                
             order by (list key sequence);
                                                               
    exec sql open MiscChg;                                     
                                                              
    exec sql fetch from MiscChg                                
             for   :MaxRows rows                              
             into  :arrMiscChg;                               
                                                               
    cntMiscChg = sqler3;                                      
                          
    exec sql close MiscChg;
                           
    return;               
 /end-free                
p LoadMiscChgArray...     
p                 e       

 

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Select distinct into array?
Posted: 4 years 7 months 6 days 13 hours 42 minutes ago

Well, yes, if you are doing embedded SQL, it's easy. You don't necessarily need a loop, though. You can SELECT directly into an array. So it depends on your array dimension vs. how many rows you expect. See SQL Embedded Programming, chapter on ILE RPG, there's a specific section on using host structure arrays.

If you're not using embedded SQL, after your read, do a SetGT using that value before you do the next read. You need an index with suitable high order keys, but otherwise this is pretty straightforward.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Select distinct into array?
Posted: 4 years 7 months 6 days 13 hours 41 minutes ago

Aha! Kudos to Neil. This is  basically what you'll find in SQL Embedded Programming, but with more detail.

Posted by: Viking
Premium member *
CA
Comment on: Select distinct into array?
Posted: 4 years 7 months 6 days 5 hours 17 minutes ago
Edited: Wed, 21 Nov, 2012 at 15:47:16 (1679 days ago)

I guess I was hoping to just be able to do this in one simple step, but it doesn't seem to work:

d locArray        s              6s 0 Dim(200) Inz

/free

Exec Sql Select distinct location into :locArray from myfile;

 

I also tried defining the array as a subfield of a DS like this and inserting into it, but I get the error that the SQL returned more than one row.

d locDS           ds          1200    Inz     
d  locArray       s              6s 0 Dim(200)

 

I think I need to play with neilrh's example when I get a few minutes.  Thanks.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Select distinct into array?
Posted: 4 years 7 months 4 days 22 hours 59 minutes ago

You can add For 200 Rows. To your select stmt.

Posted by: Viking
Premium member *
CA
Comment on: Select distinct into array?
Posted: 4 years 1 months 25 days 7 hours 33 minutes ago
Edited: Thu, 02 May, 2013 at 13:41:54 (1517 days ago)

I had to do something just like this again so I thought I'd share my simple test that works for loading values from a file into an array.  Thanks for the help.

 

      *==============================================================*                              
      *  Program Name: testsqlary                                    *                              
      *==============================================================*                              
                                                                                                    
      /Copy MYLIB/QCpySrc,Hspecs                                                                 
                                                                                                    
      /Copy MYLIB/QCpySrc,ProtoUtil                                                              
      /Copy MYLIB/QCpySrc,ConstSQL                                                               
                                                                                                    
     d maxElem         c                   200                                                      
     d maxRows         s             10i 0 Inz(maxElem)                                             
     d classCount      s              5i 0 Inz                                                      
     d i               s              5i 0 Inz                                                      
                                                                                                    
     d classArrDS      ds                  Qualified Dim(maxElem) Inz                               
     d  class                         6s 0                                                          
                                                                                                    
      /Free                                                                                         
                                                                                                    
       Exec sql
         declare MyCursor cursor for
           select distinct SQCLASS
             from fstoreqta;
                                                                                                    
       Exec sql open MyCursor;                                                                      
       Exec sql fetch from MyCursor for :MaxRows rows into :classArrDS;                             
       classCount = sqler3;                                                                         
       Exec sql close MyCursor;                                                                     
                                                                                                    
       joblog('Elements fetched = %s.':%char(classCount));                                          
                                                                                                    
       For i = 1 to classCount;                                                                     
         joblog('Element %s = %s.':%char(i):%char(classArrDS(i).class));                            
       EndFor;                                                                                      
                                                                                                    
       *inlr = *on ;                                                                                
                                                                                                    
      /End-Free