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.
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)
Execute the Select and do FETCH in a loop?
Chris Ringer
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
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.
Aha! Kudos to Neil. This is basically what you'll find in SQL Embedded Programming, but with more detail.
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.
You can add For 200 Rows. To your select stmt.
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