I have a custom swql query to pull data for pseudowire status. My query runs fine in SWQL studio but keeps returning error in Web UI. Please help
SELECT
a.rowid as rowid_a,
b.rowid as rowid_b,
a.description as description_in_node_a,
b.description as description_in_node_b,
a.id AS id_a,
b.id AS id_b,
a.caption AS caption_a,
b.caption AS caption_b,
a.operstatus AS operstatus_a,
b.operstatus AS operstatus_b,
a.uptime AS uptime_a,
b.uptime AS uptime_b
FROM (
SELECT
cp.CustomPollerStatus.RowID as rowid,
cp.Node.Caption as caption,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcDescr'
THEN cp.CustomPollerStatus.Status
END) AS description,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcOperStatus'
THEN cp.CustomPollerStatus.Status
END) AS operstatus,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcID'
THEN cp.CustomPollerStatus.Status
END) as id,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcUpTime'
THEN cp.CustomPollerStatus.Status
END) as uptime
FROM Orion.NPM.CustomPollerAssignmentOnNode as cp
WHERE cp.AssignmentName LIKE 'cpwVc%'
GROUP BY
cp.CustomPollerStatus.RowID, cp.Node.Caption
) a
JOIN (
SELECT
cp.CustomPollerStatus.RowID as rowid,
cp.Node.Caption as caption,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcDescr'
THEN cp.CustomPollerStatus.Status
END) AS description,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcOperStatus'
THEN cp.CustomPollerStatus.Status
END) AS operstatus,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcID'
THEN cp.CustomPollerStatus.Status
END) as id,
MAX(CASE
WHEN substring(cp.AssignmentName, 1, charindex(' ', cp.AssignmentName)) = 'cpwVcUpTime'
THEN cp.CustomPollerStatus.Status
END) as uptime
FROM Orion.NPM.CustomPollerAssignmentOnNode as cp
WHERE cp.AssignmentName LIKE 'cpwVc%'
GROUP BY
cp.CustomPollerStatus.RowID, cp.Node.Caption
) b
ON
a.id = b.id
ORDER BY
a.caption
