Archives of the TeradataForum

Message Posted: Tue, 21 Jan 2003 @ 20:53:35 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hello all,

Well, I've got an interesting question, I think. I was looking at a stored procedure that was brought to me today because it was running 3+ hours and never finished. The main SQL that runs once for the procedure runs 1/2 hour by itself. What happens is that the results of the main SQL returns a set of ordered sets, descending by date, each of which is processed, looking for the point at which a figure falls to or below zero.

The first row where the figure is zero should be included in the set, but not subsequent rows. So the SQL guy who did it wrote a stored procedure to cursor (hey, a new verb!) through each ordered set and at the cutoff point, write a row to another table with results.

Now, he was writing to a permanent table, and I figured this would be subject to the single row write and Transient Journal penalty (Five yards! Not thinking in sets!). He and I beat each other black and blue, attempting to come up with Other Ways To Do It. What I was thinking was that writing to a volatile table will at the very least, bypass the TJ. Can some one enlighten me on the truth of this?

Also, if you've encountered a set-based solution for this, please let me know. Wish I had my (was that two or three copies I've given away?) copy of SQL for Smarties by Celko. I humbly ask for the use of your conglomerate brain on this subject and thank you beforehand for any assistance you can lend.

iv

	<Prev	Next>		<<First	<Prev	Next>	Last>>