Re: SQL to transform Rows to Columns

I think the cursor for the /possibly existing/ table can be opened, and if the error /not found/ results, then that cursor is not opened. That is how I would test for the existence of that table. If instead that cursor opens, then that is the result set. Otherwise generate the temporary table, against which the open of the other cursor will be the result set. That is, what about this pseudo-code as representation:

<code>
create procedure mytableX
result sets 1 language sql
begin
declare which_cursor char(4) default 'PERM';
declare cursor m1 for select ... from mytable1;
declare cursor tt for select ... from temptable;
declare not_found CONDITION FOR SQLSTATE '01532';
declare continue handler for not_found
set which_cursor='TEMP';
open m1;
if which_cursor = 'TEMP' then
create global temporary table temptable <...>;
<insert data from mytable2 into temptable>;
open tt;
end if;
end
</code>

Of course I still infer a problem of /order/ in the mytable2 data. That is because, although the quoted pseudo-code includes a counter as an apparent attempt to resolve the concern, the logic is flawed unless the /recordset/ itself could be ordered. An external UDTF could use native I/O to retrieve the rows in arrival sequence [to build the one row of multiple columns], and that could be referenced in the procedure for the mytable2.

Regards, Chuck

Pete Helgren wrote:

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 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?