Archives of the TeradataForum

Message Posted: Thu, 02 Oct 2003 @ 09:09:13 GMT

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

Thats what we have done Andy (plus we could have got around the problem by using a physical table rather than a derived table anyway). I just thought it was a little odd that we could do anything at all with the column from the derived table except use it in a CASE statement in the SELECT part of the query.

The GSC have said that Derived tables are now treated in the same way as VIEWS (sorry if I have over simplified this) and therefore it is working as designed .I have managed to re create the problem using the VIEW approach, but in the case of a VIEW you can reference the Column in a CASE statement in the WHERE clause but not the SELECT which also seams odd to me.

CREATE SET TABLE mydb.test ,NO FALLBACK ,
     NO BEFORE JOURNAL,
     NO AFTER JOURNAL
     (
      col1 INTEGER,
      col2 INTEGER)
PRIMARY INDEX ( col1 );

create view mydb.testview
 (
 col1
 ,number_of_distinct
 )
 as locking mydb.test for access

 sel
 col1
 ,count(distinct col2)

 from mydb.test

 group by 1;

/*** THIS WORKS ***/
sel
col1
,number_of_distinct

from mydb.testview

where
(case when number_of_distinct=1 then 'one' else 'many' end)='many';

/*** THIS DOESN'T WORK ***/
sel
col1
,case when number_of_distinct=1 then 'one' else 'many' end

from mydb.testview;

Error 3627 Distinct Aggregate Expressions have been used in an invalid place.

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