It is important to understand that everything is working at this point so
my initial problem has been resoled. This is now a discussion of best
practices and recommendations. That being said...
Are you against writing the whole thing in the stored procedure?
I can give you a sample of where I'm doing the same thing.
I think that is what I am doing. You can send me an example of what you are
talking about so I can see if your approach is different, but my stored
procedure creates the temporary table, populates it, and returns it all
within the same module. If you are talking about using an SQL procedure
instead of a System procedure, I can't do it that way. Part of populating
my temporary table is to "massage" the data a little using some existing RPG
logic. Here's my stored procedure in a nutshell:
1. Initialize module and create my temporary table
2. Use initial SQL query to pull the data I need.
3. Loop through that cursor. Format data in certain columns using existing
RPG procedures.
4. Once a row is processed, use INSERT statement to add it to temporary
table.
5. Finally, do final query on temporary table and return result set.
That last step is where my issue was. When I ran in Run SQL Scripts, I
would run the procedure and it would return the wrong columns. But if I
queried the temporary table directly, it had been created correctly.
It also is worth mentioning that this procedure is being written to be
called by a PHP script.
midrange.com
