I have a huge view -the view gathers information of players. In a specific part the view assigns agents for players, those players are selected from table 'S' and are being assigned using a case statement. I want to assign specific agents according to a aggregated value called total_deposit in table T. so basically i want to say case make the agent name Andy when users from table S have max(total_deposit)within the conditions below (from table T) that is the code i tried:
CASE
WHEN s.Username = gs.username THEN gs.agent
when exists (select t.username from temptest t
where t.username = s.username
group by t.username
having max(t.total_deposit) >= 5000 or
( max(t.total_deposit) >= 1000 AND max(t.total_deposit) < 5000 )or
( max(t.total_deposit) >= 500 AND max(t.total_deposit) < 1000) or
( max(t.total_deposit) >= 250 AND max(t.total_deposit) < 500 )
) and s.Country in('Austria', 'Luxemburg', 'Switzerland', 'Lichtenstein')
then 'Andy'
else 'Joy'
It didn't work because if exists needs to follow with an expression.. how can i do this? thanks