I am new to teradata and I'm trying to learn via the blogs here. i have a scenario which i am unable to create.
below is the table-
|COL A||COL B||COL C (Expected)|
if the count of values in COL B is 2, then COL C should have VALUE 1, if the count is more than 3, then key in Value 2.
however if the count is 1, then put in the value from COL B.
I would really appreciate if you can help me with this question.
There are 2 solutions. I made a "test" table that looks like this:
if you are looking to count the number of total rows for each cust_id (you aren't concerned about non-distinct item names), then you can use an OLAP function inside of a CASE statement:
select cust_id ,item ,CASE WHEN count(item) over (partition by cust_id) = 2 THEN 'VALUE1' WHEN count(item) over (partition by cust_id) >=3 THEN 'VALUE2' ELSE item END FROM test;
If you need to count the number of distinct items for each cust_Id (if there are two rows for cust_id= 1 with item = 'ABC' and you want that to just count as one). then you need to use a derived table since you can't use the DISTINCT clause in OLAP functions:
SELECT a.cust_id , a.item , CASE WHEN b.item_count = 2 THEN 'VALUE1' WHEN b.item_count >=3 THEN 'VALUE2' ELSE item END FROM test a INNER JOIN (select cust_id ,count(distinct(item)) as item_count from test group by 1) b on a.cust_id = b.cust_id