Custom Query Widget and SQL UNION

jlombardo

Does a SQL UNION work in the Custom Query Widget?  I have built a query that gets stats from 3 nodes then a union to a query that totals all the columns from each node to get a grand total.  Both query's work independently.  However when I tried to use the UNION command the Widget shows a generic message. 

If I take that same query with the UNION and do a query using the SWIS API it works and returns the expected data.   Is there a reason why the UNION works in the SWIS API and not in the Custom Query Widget? 

Also how can I debug the Custom Query Widget?  There is no error information in the widget when the query fails.  Where is this information logged?

Thanks

dodo123

please add the query so we can take a look. here is a working one which may help.

SELECT 'Datastores' AS Metric, COUNT(DataStoreID) AS Value
FROM Orion.VIM.Datastores

UNION ALL

(SELECT 'Total Space (TB)' as Metric, ROUND(SUM(Capacity) / 1099511627776.0, 1) as Value
FROM Orion.VIM.Datastores)

UNION ALL

(SELECT 'Free Space (TB)' as Metric, ROUND(SUM(FreeSpace) / 1099511627776.0, 1) as Value
FROM Orion.VIM.Datastores)

UNION ALL

(SELECT 'Oversubscribed (Bytes)' as Metric, 
       CASE 
           WHEN SUM(ProvisionedSpace) > SUM(Capacity) 
                THEN (SUM(ProvisionedSpace) - SUM(Capacity))
           ELSE 0 
       END as Value
FROM Orion.VIM.Datastores)

UNION ALL

(SELECT 'Storage Full %' as Metric, 
       FLOOR(((SUM(Capacity) - SUM(FreeSpace)) * 100.0 / SUM(Capacity))) as Value
FROM Orion.VIM.Datastores)