Archives of the TeradataForum

Message Posted: Tue, 02 May 2006 @ 16:54:43 GMT

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

Sorry, but I Really don' t understand .... I've tried this select on my demo and it returns this:

SQL submitted:

     select a.col1
     >  ,a.col2
     >  ,a.col3
      >>>>>>>>>>>>  ,case when (dist_val)=1 then COL3 >>>>>>>> I keep COL3
     and not the count distinct that will be always '1'
     >  else 8
     >  end
     >  from tabA a
     >  inner join
     >  (select col1,count(distinct col3) as dist_val  from tabA  group by 1)

     > b  on a.col1=b.col1
     > order by 1;

SQL RESULTS:

     col1   col2   col3   
     123    444    1      8
     123    333    2      8
     123    222    1      8
     234    777    1      1
     234    666    1      1
     234    555    1      1
     345    999    2      2
     345    888    2      2
     345    111    2      2

The unique problem could be if col3 for a col1 has the value = 8 then you could not distinguish if the 8 stay for distinct values or all values = 8

Andrea Cappelli
NCR Italia
Teradata Professional Services
Teradata Certified Implementation Specialist V2R5

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