I have 160 labels that are pulled from a Microsoft SQL Database.
Code in Root Container pointing to the database sql query is as follows:
SELECT 0 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 19, cast(floor(cast(dateadd(day, 0, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 31, cast(floor(cast(dateadd(day, 0, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 1 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 7, cast(floor(cast(dateadd(day, 0, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 19, cast(floor(cast(dateadd(day, 0, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 2 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 19, cast(floor(cast(dateadd(day, -1, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 31, cast(floor(cast(dateadd(day, -1, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 3 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 7, cast(floor(cast(dateadd(day, -1, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 19, cast(floor(cast(dateadd(day, -1, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 4 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 19, cast(floor(cast(dateadd(day, -2, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 31, cast(floor(cast(dateadd(day, -2, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 5 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 7, cast(floor(cast(dateadd(day, -2, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 19, cast(floor(cast(dateadd(day, -2, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 6 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 19, cast(floor(cast(dateadd(day, -3, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 31, cast(floor(cast(dateadd(day, -3, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 7 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 7, cast(floor(cast(dateadd(day, -3, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 19, cast(floor(cast(dateadd(day, -3, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 8 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 19, cast(floor(cast(dateadd(day, -4, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 31, cast(floor(cast(dateadd(day, -4, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 9 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 7, cast(floor(cast(dateadd(day, -4, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 19, cast(floor(cast(dateadd(day, -4, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 10 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 19, cast(floor(cast(dateadd(day, -5, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 31, cast(floor(cast(dateadd(day, -5, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 11 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 7, cast(floor(cast(dateadd(day, -5, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 19, cast(floor(cast(dateadd(day, -5, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 12 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 19, cast(floor(cast(dateadd(day, -6, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 31, cast(floor(cast(dateadd(day, -6, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 13 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 7, cast(floor(cast(dateadd(day, -6, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 19, cast(floor(cast(dateadd(day, -6, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 14 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 19, cast(floor(cast(dateadd(day, -7, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 31, cast(floor(cast(dateadd(day, -7, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 15 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 7, cast(floor(cast(dateadd(day, -7, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 19, cast(floor(cast(dateadd(day, -7, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 16 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 19, cast(floor(cast(dateadd(day, -8, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 31, cast(floor(cast(dateadd(day, -8, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
UNION
SELECT 17 as “item”, sd.status as “Status”, description, count(dt.Status) as “Count”, sum(datediff(minute, StartTime, EndTime)) * 100 / 720.0 as “Percent”
FROM Status_Definition sd LEFT JOIN Downtime_Tracking dt ON dt.Status = sd.status AND StartTime >= dateadd(hour, 7, cast(floor(cast(dateadd(day, -8, getdate()) as float)) as datetime)) AND StartTime < dateadd(hour, 19, cast(floor(cast(dateadd(day, -8, getdate()) as float)) as datetime))
WHERE sd.status in (0,3,12,41,42)
GROUP BY description, sd.status
I am getting this error on a bunch of the labels not sure how many exactly. I am pulling info in 12 hr blocks as day and night for 7 day history with a current day summary. I am getting the following error.
Exception: Error executing expression binding on
TLN_2_TRS.Root Container.Group 9.Label 30.text
caused by NullPointerException
Ignition v7.9.2 (b2017041315)
Java: Oracle Corporation 1.8.0_121
full detail
java.lang.Exception: Error executing expression binding on
TLN_2_TRS.Root Container.Group 9.Label 30.text
at com.inductiveautomation.factorypmi.application.binding.ExpressionPropertyAdapter.runExpression(ExpressionPropertyAdapter.java:91)
at com.inductiveautomation.factorypmi.application.binding.ExpressionPropertyAdapter$1.run(ExpressionPropertyAdapter.java:58)
at com.inductiveautomation.ignition.client.util.EDTUtil$ProcessQueue.run(EDTUtil.java:113)
at java.awt.event.InvocationEvent.dispatch(Unknown Source)
at java.awt.EventQueue.dispatchEventImpl(Unknown Source)
at java.awt.EventQueue.access$500(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.awt.EventQueue$3.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.security.ProtectionDomain$JavaSecurityAccessImpl.doIntersectionPrivilege(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.run(Unknown Source)
Caused by: java.lang.NullPointerException
at com.inductiveautomation.ignition.common.expressions.DefaultFunctionFactory$RoundFunction.execute(DefaultFunctionFactory.java:1087)
at com.inductiveautomation.ignition.common.expressions.FunctionExpression.execute(FunctionExpression.java:69)
at com.inductiveautomation.ignition.common.expressions.ArithmeticExpression.execute(ArithmeticExpression.java:52)
at com.inductiveautomation.factorypmi.application.binding.ExpressionPropertyAdapter.runExpression(ExpressionPropertyAdapter.java:82)
… 16 more
Ignition v7.9.2 (b2017041315)
Java: Oracle Corporation 1.8.0_121