SQL Query - CASE Statement - URGENT

Riddhi Valecha

9y1.4kLearn iOS Programming

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tuhin Paul3y ago

Accepted answer

SQL query using a TRY...CATCH block

BEGIN TRY
    SELECT t.Device, t.SubDevice,
        CASE  
            WHEN t.Device = 'Desktop' OR t.SubDevice = 'Laptop' AND t.HostName IN ('temp', 'scrap') THEN 'N/A'
            WHEN t.Device = 'Desktop' OR t.SubDevice = 'Laptop' THEN t.HostName
            WHEN t.Device = 'Thinclient' AND t.SubDevice = 'Thinclient' THEN 'N/A'
            ELSE ''   
        END AS HostName  
    FROM tblName t
END TRY
BEGIN CATCH
    PRINT 'An error occurred: ' + ERROR_MESSAGE()
END CATCH

Tuhin Paul3y ago

You can check this

SELECT t.Device, t.SubDevice,
    CASE  
        WHEN t.Device = 'Desktop' OR t.SubDevice = 'Laptop' AND t.HostName IN ('temp', 'scrap') THEN 'N/A'
        WHEN t.Device = 'Desktop' OR t.SubDevice = 'Laptop' THEN t.HostName
        WHEN t.Device = 'Thinclient' AND t.SubDevice = 'Thinclient' THEN 'N/A'
        ELSE ''   
    END AS HostName  
FROM tblName t

Rajeev Kumar3y ago
Hi

SELECT DEVICE, SUBDEVICE, CASE WHEN DEVICE = 'Thinclient' THEN 'N/A' ELSE HostName END AS HostName

FROM tbl
Bikesh Srivastava9y ago
Hi,
I think manas's query is correct.
Manas Mohapatra9y ago
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 Valecha9y ago
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 -4,5,7.
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 - 1,2,8
When device and sub-device are "Thinclient" , then hostname must be displayed as "N/A"..

How to achieve this ??

Please guide.
Pradeep Shet9y ago
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.