Archives of the TeradataForum

Message Posted: Wed, 07 Mar 2007 @ 09:34:58 GMT

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

Goutham:

I was suffering from jet lag. You wanted to see the detail (hence the a.*) along with the aggregated data. That hit me this morning after a good night's sleep. So your best option is to use OLAP functions for the entire request and eliminate the derived table. When you do not specify the (ROWS UNBOUNDED PRECEDING) instead of seeing the progression of values, you have available the final answer immediately. This value you can use in calculations to derive the column data you are seeking - such as contribution margin.

Sorry for my initial confusion based on lack of sleep. That is why we use Teradata - detail data.

There have been a couple of very good suggestions on here for you to follow using this same approach. The OLAP functions are very powerful and provide many options now, particularly in V2R5. It is worth the read in the reference manual and seeing examples if you don't have experience using them. Prior to these functions, our only option was a derived table. The OLAP functions match the rows for us and we are not required to write the join (as indicated earlier). The Teradata world is bigger now.

Regards,

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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