Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: JI, coalesce stmnts, and group by
 
From:   Barrineau, Claybourne

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023