Archives of the TeradataForum

Message Posted: Wed, 01 Oct 2003 @ 16:27:37 GMT

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

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.

Thanks,

Gary

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

Thanks

Howard

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:

Reviewed incident.

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

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