On 26 September 2015 at 23:50, Booth Martin <booth@xxxxxxxxxxxx> wrote:
Here is where I am at:
d HDG3X120 ds
d AR1 120 din(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.
Thanks, that helps tremendously in understanding what is desired.
Vern said two things which are germane: 1 - one can't FETCH 3 rows
into a single row DS, and 2 - get freaky. He's right on both counts,
but the first point is assuming that the second point isn't in play.
Well, in my opinion, the first point is a red flag that the database
isn't set up to do what you want, but we work with the database design
we have, mostly.
This is almost like a pivot table, where rows are converted into
columns. And if you did that, you could use the resulting pivot table
as a CTE to do a SELECT LEFT CONCAT MIDDLE CONCAT RIGHT... to
construct the final 360 byte wide column. Here's one example of a
pivot table:
http://stackoverflow.com/questions/13579143/how-can-i-pivot-a-table-in-db2
Another approach is to stack up CTEs. This might be useful to you
because of the very specific nature of the table - always 3 rows,
always left, middle, right. Something like this:
with
left as
(select field3 from filep where filekey='HEADING12' and filekeyseq=1),
middle as
(select field3 from filep where filekey='HEADING12' and filekeyseq=2),
right as
(select field3 from filep where filekey='HEADING12' and filekeyseq=3)
select left.field3 concat middle.field3 concat right.field3 as bigfield
from left
join middle
join right
This assumes that FILESEQ can differentiate between the rows. If it
turns out that FILESEQ isn't always 1, 2, 3, then you can use a MAX
and MIX expression in their stead:
...and fileseq = (select min(fileseq) from filep where
filekey='HEADING12'), etc.
You can start to see how it would be better to have a distinct key for
the left, middle and right parts, like HEADING12L, HEADING12M,
HEADING12R.
A question to ponder is this: Why do this in SQL? You might be stuck
with someone else's DB design, but that doesn't mean you're stuck
accessing it only with SQL. Vanilla RPG would be pretty easy, SETLL,
READE and inside the loop do BIG += FIELD3; If the answer is that
it's educational, consider writing this as an SQL user defined
function. Basically you write the RPG with a specific parameter list,
and register this program to the database with the SQL CREATE FUNCTION
statement. The resulting SELECT would look something like
SELECT GETHEADINGS() as BIGFIELD FROM SYSIBM.SYSDUMMY1;
When the day comes that the database changes to have 4, or unlimited
headings, all you'd have to change is the UDF. Well, and all the
places you're stuffing the results, of course.
Scott Klement has a very nice resentation on SQL UDFs written in RPG:
http://www.scottklement.com/presentations/RPG%20User%20Defined%20Functions%20%26%20Table%20Functions.pdf
and so does MC Press:
http://www.mcpressonline.com/rpg/reuse-your-rpg-code-with-sql-user-defined-functions.html
--buck
midrange.com
