Re: SQL to transform Rows to Columns

[CRPence <CRPbottle@xxxxxxxxx>]

  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?