Teradata, subquery in case
<p>can you help me please. I need insert in case subquery, but teradata doesn't allow me this query, how can i change it (to similar query as case, like if)?</p>

<p><pre><code>select amount, status
case
when max(amount) then 
(select sum(a1.amount)+a2.amount
from payments a1
join  payments a2 on a1.payment_id=a2.payment_id
where a2.status = 't'
and a1.status not like 'w'
group by  a1.amount,a2.amount)
else sum(amount)
end
from payments</pre></code></p>

<p><code>Amount | Status
--------| ------
10      | t
20      | w
30      | t
40      | w
50      | t
60      | t
70      | k</code></p>

<p>expected results: if amount is max then calculate amount to new column like sum(a1.amount)+a2.amount, where status for table a1 is 't' and status for a2 is not 'w'. If amount is not max, then just calculate all amounts from table payments. </p>

<p>results: </p>

<p>--for amount = 70 insert into new column (for example result) sum(all amount where status is not 'w') + sum(all amount where status is 't'). </p>

<p>--For other amount &lt; 70 insert into new column result sum(all amount).</p>

<p>Thank you for your help.</p>