On 26-Sep-2015 21:50 -0600, Booth Martin wrote:
Here is where I am at:
d HDG3X120 ds
d AR1 120 dim(3)
...
exec sql declare C1 cursor for
select FIELD3 from FILEP where FILEKEY = 'HEADING12'
order by FILEKEYSEQ;
exec sql open C1;
exec sql fetch C1 for 3 rows into :HDG3X120;
My preferred outcome is one field that is 360 columns wide, made up
by concatenating one 120 column field from each of 3 records.
Although the SQL could be used to return the three rows as one row of
contiguous\concatenated data {as Buck had responded
[
http://archive.midrange.com/rpg400-l/201509/msg00192.html] though with
no mention of using an OLAP query}, there seems little advantage in
doing so. As Buck alludes in that reply, any changes to the database
likely would require recompiling the HLL program [as that into which
"you're stuffing the results"], irrespective any encapsulation of the
SQL [e.g. in a VIEW, UDT, or UDTF] done outside of the HLL.
Thus coding the /freaky/ method {described as such by Vern
[
http://archive.midrange.com/rpg400-l/201509/msg00191.html]}, seems to
me, to be the most appropriate. I find the method of defining storage
in multiple ways, with the use of based storage, to be quite natural;
i.e. not so /freaky/, excepting the apparent lack of an ability by the
compiler to ensure there is consistency between the multiple
declarations if changes are made to one without an appropriate change
being reflected in the other.
For an example of the RPG program effecting the three rows as one
/field/ of contiguous data:
// following is written for a 12byte field3 for PoC, so
// the C1data is _one field_ of 36bytes, while defining
// that data in the array from the quoted post; IIRC the
// DSPLY opcode will fail, as coded, with a 120byte field
// constant sizes for max returned SQL records
d dimSz C 03
// effective constants
D maxFetch S 10I00 Inz(dimSz)
D C1data@ S * Inz(*null)
// dummy declaratives to enable compile
d null@ S * inz(*null)
d filepDSext e ds extname(FILEP)
d based(null@)
// Format of returned SQL records
d* !! Reflect changes in this DS to the C1rows DS !!
D C1data DS qualified
D based(C1data@)
D* HdgElem#: one per dimSz is required!
D HdgElem1 like(FIELD3)
D HdgElem2 like(FIELD3)
D HdgElem3 like(FIELD3)
d HDG3X120 dim(dimSz)
d like(FIELD3)
d overlay(C1data)
// Stg for returned SQL records
d* !! Reflect changes in this DS to the C1data DS !!
d C1rows ds dim(dimsz) qualified
d HdgRow like(FIELD3)
// count of actual rows fetched
d fetchCt S 10U00
d*
...
exec sql open C1;
exec sql fetch C1 for :maxFetch rows into :C1rows;
fetchCt = sqlerrd(3) ; // record nbr of rows fetched
// I prefer addressing data in runtime vs initialization
C1data@ = %addr(C1rows) ; // make data accessible
// note: no handling for cond: fetchCt<3
dsply C1data.HDG3X120(1) ;
dsply C1data.HDG3X120(2) ;
dsply C1data.HDG3X120(3) ;
dsply C1data ; // as contiguous data
...
midrange.com
