CODEWITHSUNDEEP
sqlnested
SQL Novice
SQL Novice

Reputation: 323

Nested CASE statements in SQL

I am running the below SQL and I need to add a case statement for the svcState column.

I have a value defined for each number in that column which I need to have in my query. For instance 7 is OK, 4 is down etc. I tried adding this in the CASE statement as below and it seems, the syntax is incorrect. Any help will be greatly appreciated.

SELECT * FROM
(
SELECT
A.NodeName AS NodeName,
MAX(CASE WHEN Poller_Name='svcServiceName' THEN CAST(Status AS varchar) ELSE ''END) svcServiceName,
MAX(CASE (CASE WHEN Poller_Name='svcState' AND Status ='7' THEN  'OK'
         WHEN Poller_Name='svcstate' AND Status ='4' THEN 'OUT OF SERVICE' END) 
    THEN CAST(Status AS bigint) ELSE '' END) svcState
FROM
(
SELECT
Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.UniqueName AS Poller_Name, CustomNodePollerStatus_CustomPollerStatus.Status AS Status, CustomNodePollerStatus_CustomPollerStatus.rowid as row, CustomNodePollerStatus_CustomPollerStatus.RawStatus as RawStatus
FROM
((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON (Nodes.NodeID = CustomNodePollerAssignment_CustomPollerAssignment.NodeID)) INNER JOIN CustomPollers CustomNodePollers_CustomPollers ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerID = CustomNodePollers_CustomPollers.CustomPollerID)) INNER JOIN CustomPollerStatus CustomNodePollerStatus_CustomPollerStatus ON (CustomNodePollerAssignment_CustomPollerAssignment.CustomPollerAssignmentID = CustomNodePollerStatus_CustomPollerStatus.CustomPollerAssignmentID)
WHERE 
(
(CustomNodePollers_CustomPollers.UniqueName = 'svcServiceName') OR
(CustomNodePollers_CustomPollers.UniqueName = 'svcState') 
)
AND
(
(CustomNodePollerAssignment_CustomPollerAssignment.InterfaceID = 0)
)
and Nodes.Caption = '101'
)A
GROUP BY NodeName, row
--ORDER BY svcServiceName
) B

Desired Output

enter image description here

Upvotes: 0

Views: 1018

Answers (1)

MatBailie
MatBailie

Reputation: 86808

MAX(CASE WHEN Poller_Name = 'svcState' THEN (CASE WHEN status = '7' THEN 'OK' ELSE 'DOWN' END) END)

Or...

MAX(CASE WHEN Poller_Name = 'svcState' AND status = '7' THEN 'OK'
         WHEN Poller_Name = 'svcState' AND status = '4' THEN 'DOWN' END)

Or...

MAX(CASE WHEN Poller_Name != 'svcState' THEN NULL -- Assumes the poller_name is never NULL
         WHEN status       = '7'        THEN 'OK'
         WHEN status       = '4'        THEN 'DOWN'
    END)

Where there is no ELSE specified, it is implicitly ELSE NULL, and NULL values are skipped by the MAX().

Upvotes: 1

Related Questions