Archives of the TeradataForum

Message Posted: Wed, 10 Nov 2010 @ 07:46:27 GMT

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

Anomy.Anom wrote:

> I am building a JI with a goal to agg some amounts to the same level as some equivalent SQL. The SQL uses a coalesce to convert a NULL to a value of -1, then performs the grouping of subsequent amounts. Any thoughts on how to perform the coalesce and the grouping?

> Is the coalesce equivalent impossible?

Without aggregating the expression:

As long as the JI has all the components, in my experience, the optimizer will use the respective JI. If the SQL is generated by a tool, and you cannot force it to issue the Coalesce, then hide the coalesce logic in a view that sits on top of the table on which the JI is built.

With aggregation of the expression:

I don't believe you'll be able to store the results of a coalesce expression in an aggregate JI.

Hope this helps,

Clay

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