Re: SQL to transform Rows to Columns

Thanks Chuck. Good information.

Based on your reply, I think I will go with the procedure since I am on V5R3M0 though it looks like I can't avoid some conditional coding to call the procedure.

If I encapsulated the all the logic into a single procedure, is it possible to "test" for the presence of a table and proceed accordingly? (I haven't written many procedures in SQL so I am not fully aware of the capabilities). For example, if I replaced the current SQL select statement with a call to a procedure, could the procedure determine the presence or absence of the alternate tables and process the request accordingly? That way I could have a result set returned in either case but the "construction" of those tables could be conditional within the procedure itself. I would expect the "pseudo code" to look something like this:

Create a temporary table with the 9 named fields

If exists 'MyTable1' then
Insert into the temporary table:
SELECT ASJD#, ASJAB1, ASJAB2, ASJAB3, ASJAB4, ASJAB5, ASJAB6, ASJAB7,
ASJAB8 FROM MyTable1
return this record set
else
counter = 1
recordset = select ID, asjab from MyTable2

while recordset has next value

//set temporary table values
temp = recordset.getvalue(asjab);
tempID = recordset.getvalue(id);

Create a record on the first pass with an insert into
OR
update temporary table set field ordinal(counter+1) = temp where ID = tempID
return this record set
end while

end if

// end of pseudo code

I'd have to work out the details on the "else" part of the procedure but the kicker is whether or not I can test for the presence of a table using SQL syntax. Is that possible?

Pete

CRPence wrote:

The tables really need to be /corrected/, and if not, separate SQL requests in their own modules probably will be the better choice; i.e. if row_oriented_db then call procABC_rows else call procABC_cols. Although the request is an effective pivot query, those are typically used for reporting selected data, rather than for selecting data.

Note that unless ordering the rows to match the layout of the columns is possible, there will be no ability for ensuring valid selection equivalence between data as rows and data as columns. A sequence number in the "other table" would be needed, to correlate each row to its ordinal column position in the format of the ASJAB1-8 table. A file that does not reuse deleted records _could_ use RRN to ensure that, given the inserted data was *always* inserted sequentially [not as a set] in the same order; i.e. col1 to col8 always inserted as the first to eighth row, but that would be difficult to ensure, and any reorg by some key break that.

It is a relatively simple recursive query to concat the rows to make a single column, but a table function or a procedure for a result set from a temporary table would probably be required if the SELECT against the ASJAB1-8 table refers to the columns by their names. Of course the first is not an option due to the release; irrespective of its inability to effect multiple columns. The second and third limit the SELECT to read-only queries even though they can establish columns. For the second, I am not sure if there would be a way to keep the statements the same, so it is probably not an option either. For the third, it is really just a matter of changing the recently given procedure to do an insert from a select cursor of the file with the data instead of the rows generated from variables; i.e. in the message:
http://archive.midrange.com/midrange-l/200808/msg00331.html

For going from columns to rows as an unpivot query, that probably would require CASE to exclude the union all of the columns that should not become rows; per the message:
http://archive.midrange.com/rpg400-l/200807/msg00180.html

Regards, Chuck