SQL Query - CASE Statement - URGENT

Question

Hello all My Requirement is- I have a table with details of - Devices Subdevices and hostnames (all computer systems details). Now I want to display the result as - 1. When host name contains scrap or temp values then Hostname must be blank. 2. When device type is ThinClient  then Hostname must be N/A Please help me to make the query .. Eg - Original Table - ID Device SubDevice HostName 1 Desktop Desktop H1 2 Laptop Laptop H2 3 ThinClient ThinClient 4 Desktop Desktop Scrap 5 Laptop Laptop Scrap 6 ThinClient ThinClient 7 Desktop Desktop Temp 8 Laptop Laptop H3 9 ThinClient ThinClient 10 Laptop Laptop H4 Output as ID Device SubDevice HostName 1 Desktop Desktop H1 2 Laptop Laptop H2 3 ThinClient ThinClient N/A 4 Desktop Desktop 5 Laptop Laptop 6 ThinClient ThinClient N/A 7 Desktop Desktop 8 Laptop Laptop H3 9 ThinClient ThinClient N/A 10 Laptop Laptop H4

Bikesh Srivastava · Answer

Hi I think manas's query is correct.

Manas Mohapatra · Answer

Try with below query: SELECT Device SubDevice CASE WHEN Device = 'Desktop' OR SubDevice = 'Laptop' AND (HostName = 'temp' OR HostName = 'scrap' ) THEN 'N/A' WHEN Device = 'Desktop' OR SubDevice = 'Laptop' AND (HostName != 'temp' OR HostName != 'scrap' ) THEN HostName WHEN Device = 'Thinclient' AND SubDevice = 'Thinclient' THEN 'N/A' ELSE '' END AS HostName FROM tblName

Riddhi Valecha · Answer

Hi.. No.. This is not giving output.. Requirement is - When device and sub-device are Desktop or Laptop and if hostname in that record contains string temp or scrap then hostname must be displayed as empty. ID -457. When device and sub-device are Desktop or Laptop and if hostname in that record does not contains string temp or scrap then hostname must be displayed ID - 128 When device and sub-device are Thinclient  then hostname must be displayed as N/A.. How to achieve this ?? Please guide.

Pradeep Shet · Answer

hi riddi SELECT DEVICE SUBDEVICE CASE WHEN DEVICE = 'Thinclient' THEN 'N/A' ELSE HostName END AS HostName FROM tbl I hope this is what you needed please dont forget to mark it as answered.

ID	Device	SubDevice	HostName
1	Desktop	Desktop	H1
2	Laptop	Laptop	H2
3	ThinClient	ThinClient
4	Desktop	Desktop	Scrap
5	Laptop	Laptop	Scrap
6	ThinClient	ThinClient
7	Desktop	Desktop	Temp
8	Laptop	Laptop	H3
9	ThinClient	ThinClient
10	Laptop	Laptop	H4