I have a DNR Report that we need to pull min by min data on a process. This data is gathered by tags with in Ignition as well as as by a SQL database. There are 11 columns of data which most are either a date/time or a 0/1 (No/Yes). When I pull a single days worth if data, it all checks out. I compare the Ignition report the to SQL database and it is fine. When I go to pull 10 days/30 days/etc of data, then the report does not pull the correct information for the same time frame. Both my and my developer cannot figure out why it is doing this. Please help. 
Please give us enough information.
- What is the data table structure?
- What is the query?
Data Table Structure is SQL vis stored procedures.
There is 1 Query, and 9 scripts (set in blocks for visual ease)
Query:
header = ['RTO Online','Process Running','Over 200fpm', 'Dampers Correct', 'Running Properly','Clean Mode']
filteredDataset = []
mainQuery = data['query'].getNestedQueryResults()
subQuery = mainQuery['Block1_sub_query']
## for subQuery in mainQuery:
for child in subQuery:
rawDataset = child.getCoreResults()
# data['raw'] = rawDataset
# data['totalrows'] =rawDataset.rowCount
# build the new pydataset out of only some of the Area Data's data keys
for row in range(rawDataset.rowCount):
rtoOnline = rawDataset.getValueAt(row,4)
if rtoOnline:
rtoOnlineDisp = "Yes"
else:
rtoOnlineDisp = "No"
cleanMode = rawDataset.getValueAt(row,16)
if cleanMode:
cleanModeDisp = "Yes"
else:
cleanModeDisp = "No"
processRunning = rawDataset.getValueAt(row,7)
if processRunning:
processRunningDisp = "Yes"
else:
processRunningDisp = "No"
over200 = rawDataset.getValueAt(row,10)
if over200:
over200Disp = "Yes"
else:
over200Disp = "No"
dampersCorrect = rawDataset.getValueAt(row,13)
if dampersCorrect:
dampersCorrectDisp = "Yes"
else:
dampersCorrectDisp = "No"
runningProperly = rawDataset.getValueAt(row,14)
if runningProperly:
runningProperlyDisp = "Yes"
else:
runningProperlyDisp = "No"
filteredDataset.append([rtoOnlineDisp,processRunningDisp,over200Disp,dampersCorrectDisp,runningProperlyDisp,cleanModeDisp])
filteredDataset = system.dataset.toDataSet(header, filteredDataset)
data['Block1Data'] = filteredDataset
Script 1: All of the scripts are the same but different 3 hrs blocks of 1min segments.
preview of the report. Use to avoid slow queries and calculations
to keep previews quick.
"""
header = ['RTO Online','Process Running','Over 200fpm', 'Dampers Correct', 'Running Properly','Clean Mode']
filteredDataset = []
mainQuery = data['query'].getNestedQueryResults()
subQuery = mainQuery['Block1_sub_query']
## for subQuery in mainQuery:
for child in subQuery:
rawDataset = child.getCoreResults()
# data['raw'] = rawDataset
# data['totalrows'] =rawDataset.rowCount
# build the new pydataset out of only some of the Area Data's data keys
for row in range(rawDataset.rowCount):
rtoOnline = rawDataset.getValueAt(row,4)
if rtoOnline:
rtoOnlineDisp = "Yes"
else:
rtoOnlineDisp = "No"
cleanMode = rawDataset.getValueAt(row,16)
if cleanMode:
cleanModeDisp = "Yes"
else:
cleanModeDisp = "No"
processRunning = rawDataset.getValueAt(row,7)
if processRunning:
processRunningDisp = "Yes"
else:
processRunningDisp = "No"
over200 = rawDataset.getValueAt(row,10)
if over200:
over200Disp = "Yes"
else:
over200Disp = "No"
dampersCorrect = rawDataset.getValueAt(row,13)
if dampersCorrect:
dampersCorrectDisp = "Yes"
else:
dampersCorrectDisp = "No"
runningProperly = rawDataset.getValueAt(row,14)
if runningProperly:
runningProperlyDisp = "Yes"
else:
runningProperlyDisp = "No"
filteredDataset.append([rtoOnlineDisp,processRunningDisp,over200Disp,dampersCorrectDisp,runningProperlyDisp,cleanModeDisp])
filteredDataset = system.dataset.toDataSet(header, filteredDataset)
data['Block1Data'] = filteredDataset
Maybe I'm missing something but those aren't (SQL) queries; they are scripts that seem to be doing some work on a dataset.
Tip: you could condense the code using the ternary operator:
rtoOnlineDisp = "Yes" if rtoOnline else rtoOnlineDisp = "No"
cleanMode = rawDataset.getValueAt(row,16)
cleanModeDisp = "Yes" if cleanMode else cleanModeDisp = "No"
But really much of that conversion could be done at SQL level.
@Transistor ...Sorry.. the Query is this....
select distinct convert(varchar, Timestamp, 101) as Time FROM [Line3].[dbo].[PAINT_LOG] where Timestamp between ? and ? order by convert(varchar, Timestamp, 101)
If I try to pull more that 1 day... the data doesn't update correctly.. almost like it cannot look at it fast enough to update the values correctly.. is that a thing... I would HATE to run daily data for 2 years to make this work.
I tried the code suggested above.. i get a "cant assign to conditional expression" error.
Ordering by a stringified date is always going to be bogus, especially when months come first in the string.
But you also need to show the actual subquery.
1 Like
This was done WAY before me and at the moment, we just want to make the DNR happy.. Below is the SQL query we are referencing. If I pull more than a day, it does not work right even though the SQL data is correct. The Ignition report bugs out and defaults to Yes when it should be No
USE [Line3]
GO
/****** Object: StoredProcedure [dbo].[Report_1_Minute_Clean] Script Date: 5/16/2024 2:01:35 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Report_1_Minute_Clean]
@Start_Date Datetime,
@End_Date Datetime,
@Process integer
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #Temp_Data(
Block int,
Timestamp datetime,
Temperature real,
Negative_Pressure real,
RTO_Online bit,
Flow_Rate real,
Minimum_Flow_Rate real,
Process_Running bit,
NDO real,
Velocity real,
Average_Velocity real,
Over_200_fpm bit,
Avg_Over_200_fpm bit,
Dampers_Open_to_Oxi bit,
Dampers_Open_to_ATM bit,
Dampers_Correct bit,
Dampers_Incorrect_Over_15 bit,
Process_Running_Correct bit,
Process_Running_Correct2 bit,
Three_Consecutive_No bit,
Mainline_Clean_Mode bit,
Starting_5_Minutes bit
)
DECLARE @Flow_Constant integer
IF @Process = 9
Begin
Select Top 1 @Flow_Constant = flow_constant from [parameters]
Select @Flow_Constant = isnull(@Flow_Constant,0)
Insert Into #Temp_Data(timestamp, Temperature, Negative_Pressure, RTO_Online, Flow_Rate, Minimum_Flow_Rate, NDO, Dampers_Open_to_Oxi, Dampers_Open_to_ATM, Process_Running, Mainline_Clean_Mode, Starting_5_Minutes)
SELECT Timestamp, RETENTION_CHAMBER_TEMP_1, NEGATIVE_PRESSURE, RTO_ONLINE, (RTO_FLOW_2 - FLEXLINE_FLOW_1 - CEFLA_FLOW_1 - @Flow_Constant), Mainline_flow_min, MAINLINE_NDO,
iif((DMP_1_2_OPN_OXI = 1 AND DMP_1_2_CLSD_ATM = 1 AND DMP_3_4_OPN_OXI = 1 AND DMP_3_4_CLSD_ATM = 1 AND DMP_5_6_OPN_OXI = 1 AND DMP_5_6_CLSD_ATM = 1 AND DMP_7_8_OPN_OXI = 1 AND DMP_7_8_CLSD_ATM = 1 AND DMP_9_10_OPN_OXI = 1 AND DMP_9_10_CLSD_ATM = 1 AND DMP_OVEN_OPN_OXI = 1 AND DMP_OVEN_CLSD_ATM = 1),1,0),
iif((DMP_1_2_CLSD_OXI = 1 AND DMP_1_2_OPN_ATM = 1 AND DMP_3_4_CLSD_OXI = 1 AND DMP_3_4_OPN_ATM = 1 AND DMP_5_6_CLSD_OXI = 1 AND DMP_5_6_OPN_ATM = 1 AND DMP_7_8_CLSD_OXI = 1 AND DMP_7_8_OPN_ATM = 1 AND DMP_9_10_CLSD_OXI = 1 AND DMP_9_10_OPN_ATM = 1 AND DMP_OVEN_CLSD_OXI = 1 AND DMP_OVEN_OPN_ATM = 1),1,0),
MAINLINE_PROCESS_ON, CLEAN_MODE_ENABLE, 0
from PAINT_LOG
Where timestamp between @start_Date and dateadd(day,1,@End_Date)
End
IF @Process = 40
Begin
Insert Into #Temp_Data(timestamp, Temperature, Negative_Pressure, RTO_Online, Flow_Rate, Minimum_Flow_Rate, NDO, Dampers_Open_to_Oxi, Dampers_Open_to_ATM, Process_Running, Starting_5_Minutes)
SELECT Timestamp, RETENTION_CHAMBER_TEMP_1, NEGATIVE_PRESSURE, RTO_ONLINE, FLEXLINE_FLOW_1, FLEXLINE_FLOW_MIN, FLEXLINE_NDO,
iif((DMP_OFFLN_OPN_OXI = 1 AND DMP_OFFLN_CLSD_ATM = 1),1,0), iif((DMP_OFFLN_CLSD_OXI = 1 AND DMP_OFFLN_OPN_ATM = 1),1,0),
iif((FLEXLINE_ON = 1),1,0), 0
from PAINT_LOG
Where timestamp between @start_Date and dateadd(day,1,@End_Date)
End
IF @Process = 25
Begin
Insert Into #Temp_Data(timestamp, Temperature, Negative_Pressure, RTO_Online, Flow_Rate, Minimum_Flow_Rate, NDO, Dampers_Open_to_Oxi, Dampers_Open_to_ATM, Process_Running, Starting_5_Minutes)
SELECT Timestamp, RETENTION_CHAMBER_TEMP_1, NEGATIVE_PRESSURE, RTO_ONLINE, CEFLA_FLOW_1, CEFLA_FLOW_MIN, CEFLA_NDO,
iif((DMP_CEFLA_OPN_OXI = 1 AND DMP_CEFLA_CLSD_ATM = 1),1,0), iif((DMP_CEFLA_CLSD_OXI = 1 AND DMP_CEFLA_OPN_ATM = 1),1,0),
iif((CEFLA_NORTH_ON = 1 AND CEFLA_SOUTH_ON = 1),1,0), 0
from PAINT_LOG
Where timestamp between @start_Date and dateadd(day,1,@End_Date)
End
Update #Temp_data Set Block = 1 Where datepart(hour,timestamp) between 0 and 2
Update #Temp_data Set Block = 2 Where datepart(hour,timestamp) between 3 and 5
Update #Temp_data Set Block = 3 Where datepart(hour,timestamp) between 6 and 8
Update #Temp_data Set Block = 4 Where datepart(hour,timestamp) between 9 and 11
Update #Temp_data Set Block = 5 Where datepart(hour,timestamp) between 12 and 14
Update #Temp_data Set Block = 6 Where datepart(hour,timestamp) between 15 and 17
Update #Temp_data Set Block = 7 Where datepart(hour,timestamp) between 18 and 20
Update #Temp_data Set Block = 8 Where datepart(hour,timestamp) between 21 and 23
Update #Temp_data Set Velocity = iif((NDO = 0), 0, Flow_Rate / NDO)
Update #Temp_data Set Average_Velocity = (Select avg(Velocity) from #Temp_data Avg_Table
Where day(#Temp_data.timestamp) = day(Avg_Table.timestamp) and month(#Temp_data.timestamp) = month(Avg_Table.timestamp) and
year(#Temp_data.timestamp) = year(Avg_Table.timestamp) and #Temp_data.Block = Avg_Table.Block
Group by day(Avg_Table.timestamp), month(Avg_Table.timestamp), year(Avg_Table.timestamp), Avg_Table.Block)
Update #Temp_data set Process_Running = isnull(Process_Running,0), Mainline_Clean_Mode = isnull(Mainline_Clean_Mode,0)
IF @Process = 40
Begin
Update #Temp_Data Set Over_200_fpm = iif((Velocity > 200), 1, 0), Avg_Over_200_fpm = iif((Average_Velocity > 200), 1, 0), Dampers_Correct = iif(((Velocity > 200 AND RTO_Online = 1 AND Dampers_Open_to_Oxi = 1) OR (Process_Running = 0)),1,0)
Update #Temp_Data Set Process_Running_Correct = iif((((RTO_Online = 1) AND (Over_200_fpm = 1) AND (Dampers_Correct = 1)) OR (Process_Running = 0)),1,0), Process_Running_Correct2 = iif((((RTO_Online = 1) AND ((Avg_Over_200_fpm = 1) OR (Over_200_fpm = 1)) ) OR (Process_Running = 0)),1,0)
End
Else
Begin
If @Process = 9
Begin
Update #Temp_Data Set Over_200_fpm = iif((Velocity > 200), 1, 0), Avg_Over_200_fpm = iif((Average_Velocity > 200), 1, 0), Dampers_Correct = iif(((Dampers_Open_to_Oxi = 1) OR (Process_Running = 0 AND Mainline_Clean_Mode = 0) OR (Mainline_Clean_Mode = 1)),1,0)
Update #Temp_Data Set Process_Running_Correct = iif((((RTO_Online = 1) AND (Over_200_fpm = 1) AND (Dampers_Correct = 1)) OR ((Process_Running = 0) AND (Mainline_Clean_Mode = 0))),1,0) , Process_Running_Correct2 = iif((((RTO_Online = 1) AND ((Avg_Over_200_fpm = 1) OR (Over_200_fpm = 1)) ) OR ((Process_Running = 0) AND (Mainline_Clean_Mode = 0))),1,0)
End
Else
Begin
Update #Temp_Data Set Over_200_fpm = iif((Velocity > 200), 1, 0), Avg_Over_200_fpm = iif((Average_Velocity > 200), 1, 0), Dampers_Correct = iif(((Process_Running = 1 AND Dampers_Open_to_Oxi = 1) OR (Process_Running = 0)),1,0)
Update #Temp_Data Set Process_Running_Correct = iif((((RTO_Online = 1) AND (Over_200_fpm = 1) AND (Dampers_Correct = 1)) OR (Process_Running = 0)),1,0), Process_Running_Correct2 = iif((((RTO_Online = 1) AND ((Avg_Over_200_fpm = 1) OR (Over_200_fpm = 1)) ) OR (Process_Running = 0)),1,0)
End
End
Update #Temp_Data Set Three_Consecutive_No = 0
Declare @Timestamp datetime
Declare @Running_Correct bit
Declare @Consecutive_No int
Declare @Dampers_Correct bit
Declare @Process_Running bit
Declare @Dampers_Cons_No int
Declare @Startup_Count int
Declare Process_Run_Cursor Cursor For
Select Timestamp, Process_Running_Correct2, Dampers_Correct, Process_Running From #Temp_Data
Order by Timestamp
Select @Consecutive_No = 0, @Startup_Count = 5
Open Process_Run_Cursor
Fetch Next from Process_Run_Cursor into @Timestamp, @Running_Correct, @Dampers_Correct, @Process_Running
While @@FETCH_STATUS = 0
BEGIN
If @Process_Running = 1
Begin
If @Startup_Count < 5
Begin
Select @Startup_Count = @Startup_Count + 1, @Running_Correct = 1
Update #Temp_Data Set Process_Running_Correct = 1, Starting_5_Minutes = 1, Over_200_fpm = 1, Dampers_Correct = 1 Where Timestamp = @Timestamp
End
End
ELSE
Begin
Select @Startup_Count = 0
End
If @Running_Correct = 0
Begin
Select @Consecutive_No = @Consecutive_No + 1
End
Else
Begin
Select @Consecutive_No = 0
End
If @Consecutive_No > 2
Begin
Update #Temp_Data Set Three_Consecutive_No = 1 Where Timestamp = @Timestamp
End
If @Dampers_Correct = 0 AND @Process_Running = 1
Begin
Select @Dampers_Cons_No = @Dampers_Cons_No + 1
End
Else
Begin
Select @Dampers_Cons_No = 0
End
If @Dampers_Cons_No > 14
Begin
Update #Temp_Data Set Three_Consecutive_No = 1 Where Timestamp = @Timestamp
End
Fetch Next from Process_Run_Cursor into @Timestamp, @Running_Correct, @Dampers_Correct, @Process_Running
END
Close Process_Run_Cursor
Deallocate Process_Run_Cursor
Select Block, Timestamp, round(Temperature,0) as Temperature,round(Negative_Pressure,2) as Negative_Pressure, RTO_Online,
round(flow_rate,0) as Flow_Rate,round(minimum_flow_rate,1) as Minimum_Flow_Rate,
Process_Running,round(NDO,2) as NDO,round(velocity,0) as Velocity, Over_200_fpm,Dampers_Open_to_Oxi, Dampers_Open_to_ATM, Dampers_Correct,
Process_Running_Correct, Three_Consecutive_No, Mainline_Clean_Mode, Starting_5_Minutes from #Temp_Data order by timestamp
drop table #Temp_Data
END
Oy!
Temporary tables are evil when a caller can parallelize.
Try to rework that into a procedure that uses a Common Table Expression instead of a temporary table.
ummm... for us Lay-people... are you suggesting that i try to rewrite the Ignition code to do this... or the SQL stored procedure? either way... my guy is goingto have to do it. i would drown.
Either way.
I tend to not use stored procedures unless there's some particular performance issue that is only solved by reducing round trips.
FWIW.
When you rewrite this, try to avoid using any inserts or other data modification. Just SELECT, nested SELECT, UNION, et cetera. Perhaps with a CTE at the top.
to the root... the issue with this is that if i pull more than 1 day (1440*11) of data, when the system goes from Yes to No.. it holds Yes forever. Is this an Ignition issue or a report issue? will a rewrite even solve the problem?
It appears that your stored procedure cannot be called in parallel. It looks like that is your flaw.
This flaw is common when temporary or intermediate tables are used in a procedure.
so how do i fix it? what modifications do i need to make in the script. If i fix one.. then they all get fixed...
Well, a carefully designed query won't need scripting. Start by replacing the stored procedure.
(Someone will have to figure out how it actually works to produce a clean re-implementation. That's a bit much to ask of volunteers on a forum.)
a suggestion on where to start is helpful if I cannot change the stored procedure, what would be Plan B?
Everything that happens in a stored procedure can be broken out into individual statements and the results assembled in the gateway.
Effectively abandoning the stored procedure.
Plan C.... if i cannot abandon the stored procedure and have to keep it (it is the historical set up)... what do we need to do to make it work? Is that it can't with your comment above about not being able to parallel the data or is there a work around to make it work for more than a day but less than sat a month? trying to explain this to my boss as best I can.
You need someone to spend more time analyzing your spaghetti than I have for you
ok... and I think it more like spaghetti that has been blended. Thanks for the help