Comment utiliser Case dans SQL?

J'ai une question

Select Id,DeviceId,TankCount,Tank1_Level,Tank2_Level,ReadTime from Table Id TankCount DeviceId Tank1_Level Tank2_Level ReadTime 1 1 123 20 50 2014-11-07 14:39:33.277 2 2 456 52 78 2014-11-07 14:39:33.277 3 1 789 44 50 2014-11-07 14:39:33.277 

Tank2_Level est 50 dans tous TankCount = 1 lignes.

Je ne veux pas afficher Tank2_Level lorsque la valeur est égale à 50.

TankCount int, Tank1_Level int, Tank2_Level int.

 Id TankCount DeviceId Tank1_Level Tank2_Level ReadTime 1 1 123 20 null or empty 2014-11-07 14:39:33.277 2 2 456 52 78 2014-11-07 14:39:33.277 3 1 789 44 null or empty 2014-11-07 14:39:33.277 

 SELECT Id, DeviceId, TankCount, Tank1_Level, CASE WHEN Tank2_Level = '50' and TankCount != '2' THEN NULL ELSE Tank2_Level END AS [Tank2_Level], ReadTime FROM table 
 SELECT Id, DeviceId, TankCount, Tank1_Level, CASE WHEN TankCount = 1 AND Tank2_Level = '50' THEN NULL ELSE Tank2_Level END AS Tank2_Level, ReadTime FROM Table 

Tank2_Level est- Tank2_Level un nombre ou une string? Laissez-moi supposer que c'est un nombre:

 select <other columns>, (case when Tank2_Level = 50 then 'null or empty' else cast(Tank2_Level as varchar(255)) end) as Tank2_Level from . . .; 
 SELECT Id ,DeviceId ,TankCount ,Tank1_Level ,(CASE Tank2_Level WHEN 50 THEN NULL ELSE Tank2_Level END) ,ReadTime FROM TABLE 

Vous pouvez le simplifier en utilisant IIF

 Select Id, DeviceId, TankCount, Tank1_Level, iif((Tank2_Level=50 and TankCount=1),null,Tank2_Level) as Tank2_Level, ReadTime from Table 
 SELECT Tank2_Level = CASE WHEN TankCount >= 2 THEN Tank2_Level END FROM Table