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