Five Become One

I got a call from my team lead yesterday. He said he was swamped with the task that is due in a few days. He needed help writing a stored procedure to create a temporary table. The query was apparently all ready. It just needed to do it in PL/SQL. Although I was busy, I jumped at the opportunity.

Later I got the specs for the task. It was more complicated than I thought. there were a total of 5 queries. This was tricky because each of the 5 queries produced different columns of results. However they all needed to be combined into a big table.

I decided there was no way to CREATE a table AS SELECT FROM because there were multiple queries. So I determined the list of columns, and manually created the table with code. The create statement was so huge that I had to break the table creation down into one CREATE TABLE and multiple ALTER TABLE dynamic statements.

It was quite a chore to handle all the cases where a row in the resultant big table was sparse. Then I ran into more trouble when my dynamic statements to update pieces of the resultant table choked on some NULL inputs. That required some custom logic. By the time I was done, my script was around 1000 lines long. I did not leave work until midnight. What a chore.