Archives of the TeradataForum
Message Posted: Wed, 01 Oct 2003 @ 16:27:37 GMT
Have any of you out there had experience of / any views on the following?
We think that it would be better if the Teradata optimiser was changed such that it chooses whether Derived Tables are materialised or not depending upon efficiency and functionality.
Extract from my colleague's e-mail to our support contact :
Basically, when using a derived table to store results of a COUNT DISTINCT we can't then apply a CASE statement to the column in another part of the query. We get the error
Code = 3627 DISTINCT Aggregate Expressions have been used in an invalid place.
This didn't cause a problem under V2R4.1.3 (the user could run their problem query prior to the upgrade)
We can apply other conditions to the column such as tests in the WHERE clause and can use a CASE statement when other aggregated functions such as COUNT(*) or SUM have been used, but not COUNT DISTINCT.
See SQL below for an example
create table test(col1 int,col2 int) insert into test (1,1); insert into test(1,2); /*** this runs fine ***/ sel col1 ,number_of_distinct from ( sel col1 ,count(distinct col2) from test group by 1 ) as t1(col1,number_of_distinct) where number_of_distinct>1; /*** this gives the error ***/ sel col1 ,case when number_of_distinct>1 then 'many' else 'one' end from ( sel col1 ,count(distinct col2) from test group by 1 ) as t1(col1,number_of_distinct); /*** this runs fine ***/ sel col1 ,case when number_of_distinct>1 then 'many' else 'one' end from ( sel col1 ,count(*) from test group by 1 ) as t1(col1,number_of_distinct);
Extract from the reply from the GSC :
Current Activity or Status:
Duplicated problematic scenario reported incident.
The change we implemented in V2R5 is to treat a derived table like a view. Meaning simple derived table/view won't be necessary to be materialized.
I put derived table into view and performed same operation. I got 3627 error even on V2R4.
Based on these, I think this is working as designed. If customer requires that optimizer treat derived table just like it used to in V2R4, they need to turn
44. SpoolDerivedTables = TRUE
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|