SQL: case when statement with over (partition by)
<p>I'm quite new here and have tried various hints from Stackoverflow posts on SQL but haven't been able to solve this one.
I have a table that is result of tables joined, looks like this</p>

<p>Table A</p>

<pre><code>cust_id  prod_type

 001         A
 001         A
 002         A
 002         B
 003         A
 003         C
</code></pre>

<p>I need to apply logic: If for each cust_id  there is at least one value where prod_type is B or C, then return corresponding prod_type value. If for each cust_id all values of prod_type are A, return A.</p>

<p>The final output i am trying to get  is</p>

<p>Table B</p>

<pre><code>cust_id     prod_type

 001         A
 002         B
 003         C
</code></pre>

<p>I have tried using </p>

<pre><code>SELECT 
A.cust_id 
,CASE WHEN prod_type in ('B', 'C') THEN prod_type OVER (PARTITION BY A.cust_id)
 ELSE 'A' OVER (PARTITION BY A.cust_id) END AS product 
FROM ([Joined Tables]) AS A
</code></pre>

<p>and it seems that teradata does not allow to use over(clause) in a case statement: expects 'END' keyword between prod_type and OVER keyword.</p>