|
|
Archives of the TeradataForum
Message Posted: Tue, 02 May 2006 @ 09:53:49 GMT
Subj: | | Re: Need Help Writing SQL |
|
From: | | Anomy Anom |
<-- Anonymously Posted: Tuesday, May 02, 2006 05:09 -->
Andrea
Thanks for your suggestion, however your solution will not yield the desired result as, if all the col3 values for a particular col1 values
were '2', then count (distinct).. will be '1' and therefore col4 would be incorrectly computed to be a '1'. The following solution, using Ordered
OLAP function gives the desired results:
select a.Col1
, a.Col2
, a.Col3
, (CASE (MAX(a.Col3) OVER (PARTITION BY a.Col1))
WHEN 0 THEN 8
WHEN 1 THEN (CASE(MIN(a.Col3) OVER (PARTITION BY a.Col1) )
WHEN 1 THEN 1
ELSE 8 END)
WHEN 2 THEN (CASE(MIN(a.Col3) OVER (PARTITION BY a.Col1) )
WHEN 2 THEN 2
ELSE 8 END)
ELSE 8
END) Col4
from TabA a
Best Regards
| |