Archives of the TeradataForum

Message Posted: Tue, 26 May 2009 @ 20:13:48 GMT

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

The error message pretty much says it all: "in join index DDL,only columns,extract,sum,or,count expressions with aliases are allowed in select list."

An alternative is to create a View that includes a derived table referencing COUNT and SUM columns from the underlying tables. In this case the corresponding AJI will be used in all cases to support queries referencing the View. Where this comes in handy is when the query uses aggregate functions other than SUM and COUNT, such as MIN/MAX or ordering functions.

The underlying reason to do this is to have the query use the smaller pre-joined AJI to satisfy the MIN/MAX query rather than to rollup the underlying detail tables. Note that accessing the View will always build the derived table, even when this step would be unnecessary for queries that only use COUNT and SUM functions. As a result, using this derived table View in all cases would add some processing overhead.

Example:

     replace View dbname.os_prod_cnt_maxV AS

     SELECT COUNT(*)(FLOAT, NAMED CountStar ),
     Product_Line ,
     Product_Category ,
     Product_Group,
     Product_Name,
     sum(Quantity) (float, named Quantity),
     sum(Total_Retail_Price) (float, named Total_Retail_Price),
     count(count_Quantity) (float, named count_Quantity)
     From
     (
     SELECT COUNT(*)(FLOAT, NAMED CountStar ),
     p.Product_Line ,
     p.Product_Category ,
     p.Product_Group,
     p.Product_Name,
     sum(o.Quantity) (float, named Quantity),
     sum(o.Total_Retail_Price) (float, named Total_Retail_Price),
     count(o.Quantity) (float, named count_Quantity)
     from dbname.order_fact o
     ,dbname.product_dim p
     where o.product_id = p.product_id
     GROUP BY
     p.Product_Line ,
     p.Product_Category ,
     p.Product_Group,
     p.Product_Name
     ) d1
     Group by 2,3,4,5

And a Select statement this View supports:

     Select "Product_Name"
     , sum("Quantity")
     , max("Quantity")
     from
     dbname.os_prod_cnt_maxV
     group by  "Product_Name"

John

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