dpQuery with GROUP BY

Find and share HowTos to various installations / configurations!
Search

Post Reply
1 post • Page 1 of 1
NikolayLevchenko
Posts: 74
Joined: Wed May 10, 2017 1:34 pm

dpQuery with GROUP BY

Post by NikolayLevchenko » Tue Aug 11, 2020 2:45 pm

Hi, has anyone managed to make use of data grouping with dpQuery?

I would like to get values from my Oracle DB and make them into averaged by e.g. 1-minute intervals.

WinCC OA Help seems to suggest that this is when GROUP BY would come into play but I can;t seem to make it work. Moreover, I get completely different looking results with queryRDBdirect=0 vs. queryRDBdirect=1

Example: I've set three values over time period and here's what I get:
queryRDBdirect=1

Code: Select all

WCCOAui7:2020.08.11 16:35:05.688["SELECT '_original.._stime', '_original.._value' FROM 'AlarmCounter.status.Typ_Alert' TIMERANGE(\"2020.08.10 00:00:00\",\"2020.08.11 18:00:00\",1,0) SORT BY 0"]
WCCOAui7:2020.08.11 16:35:05.833[dyn_dyn_anytype 4 items
WCCOAui7:     1: dyn_anytype 3 items
WCCOAui7:	     1:  (Type: 0 Sys: 0 Dp: 0 El: 0 : 0..0)
WCCOAui7:	     2: :_original.._stime (Type: 0 Sys: 0 Dp: 0 El: 0 : _original.._stime)
WCCOAui7:	     3: :_original.._value (Type: 0 Sys: 0 Dp: 0 El: 0 : _original.._value)
WCCOAui7:     2: dyn_anytype 3 items
WCCOAui7:	     1: NSP2_P:AlarmCounter.status.Typ_Alert (Type: 0 Sys: 1 Dp: 4690 El: 6 : 0..0)
WCCOAui7:	     2: 2020.08.11 15:48:55.167000000
WCCOAui7:	     3: 25
WCCOAui7:     3: dyn_anytype 3 items
WCCOAui7:	     1: NSP2_P:AlarmCounter.status.Typ_Alert (Type: 0 Sys: 1 Dp: 4690 El: 6 : 0..0)
WCCOAui7:	     2: 2020.08.11 15:48:57.308000000
WCCOAui7:	     3: 35
WCCOAui7:     4: dyn_anytype 3 items
WCCOAui7:	     1: NSP2_P:AlarmCounter.status.Typ_Alert (Type: 0 Sys: 1 Dp: 4690 El: 6 : 0..0)
WCCOAui7:	     2: 2020.08.11 15:56:48.641000000
WCCOAui7:	     3: 7
WCCOAui7:]
WCCOAui7:2020.08.11 16:35:05.833["SELECT 'AVG(_original.._value)', 'MIN(_original.._value)' FROM 'AlarmCounter.status.Typ_Alert' TIMERANGE(\"2020.08.10 00:00:00\",\"2020.08.11 18:00:00\",1,0) SORT BY 0 GROUP BY SECS(60)"]
WCCOAui7:2020.08.11 16:35:06.044[dyn_dyn_anytype 2 items
WCCOAui7:     1: dyn_anytype 2 items
WCCOAui7:	     1: "AVG(_original.._value)"
WCCOAui7:	     2: "MIN(_original.._value)"
WCCOAui7:     2: dyn_anytype 2 items
WCCOAui7:	     1: 22.333333333333
WCCOAui7:	     2: 7
WCCOAui7:]
queryRDBdirect=0

Code: Select all

WCCOAui7:2020.08.11 16:35:55.701["SELECT '_original.._stime', '_original.._value' FROM 'AlarmCounter.status.Typ_Alert' TIMERANGE(\"2020.08.10 00:00:00\",\"2020.08.11 18:00:00\",1,0) SORT BY 0"]
WCCOAui7:2020.08.11 16:35:55.772[dyn_dyn_anytype 4 items
WCCOAui7:     1: dyn_anytype 3 items
WCCOAui7:	     1:  (Type: 0 Sys: 0 Dp: 0 El: 0 : 0..0)
WCCOAui7:	     2: :_original.._stime (Type: 0 Sys: 0 Dp: 0 El: 0 : _original.._stime)
WCCOAui7:	     3: :_original.._value (Type: 0 Sys: 0 Dp: 0 El: 0 : _original.._value)
WCCOAui7:     2: dyn_anytype 3 items
WCCOAui7:	     1: NSP2_P:AlarmCounter.status.Typ_Alert (Type: 1321 Sys: 1 Dp: 4690 El: 6 : 0..0)
WCCOAui7:	     2: 2020.08.11 15:48:55.167000000
WCCOAui7:	     3: 25
WCCOAui7:     3: dyn_anytype 3 items
WCCOAui7:	     1: NSP2_P:AlarmCounter.status.Typ_Alert (Type: 1321 Sys: 1 Dp: 4690 El: 6 : 0..0)
WCCOAui7:	     2: 2020.08.11 15:48:57.308000000
WCCOAui7:	     3: 35
WCCOAui7:     4: dyn_anytype 3 items
WCCOAui7:	     1: NSP2_P:AlarmCounter.status.Typ_Alert (Type: 1321 Sys: 1 Dp: 4690 El: 6 : 0..0)
WCCOAui7:	     2: 2020.08.11 15:56:48.641000000
WCCOAui7:	     3: 7
WCCOAui7:]
WCCOAui7:2020.08.11 16:35:55.772["SELECT 'AVG(_original.._value)', 'MIN(_original.._value)' FROM 'AlarmCounter.status.Typ_Alert' TIMERANGE(\"2020.08.10 00:00:00\",\"2020.08.11 18:00:00\",1,0) SORT BY 0 GROUP BY SECS(60)"]
WCCOAui7:2020.08.11 16:35:55.835[dyn_dyn_anytype 4 items
WCCOAui7:     1: dyn_anytype 3 items
WCCOAui7:	     1:  (Type: 0 Sys: 0 Dp: 0 El: 0 : 0..0)
WCCOAui7:	     2: :_original.._value (Type: 0 Sys: 0 Dp: 0 El: 0 : _original.._value)
WCCOAui7:	     3: :_original.._value (Type: 0 Sys: 0 Dp: 0 El: 0 : _original.._value)
WCCOAui7:     2: dyn_anytype 3 items
WCCOAui7:	     1: NSP2_P:AlarmCounter.status.Typ_Alert (Type: 1321 Sys: 1 Dp: 4690 El: 6 : 0..0)
WCCOAui7:	     2: 25
WCCOAui7:	     3: 25
WCCOAui7:     3: dyn_anytype 3 items
WCCOAui7:	     1: NSP2_P:AlarmCounter.status.Typ_Alert (Type: 1321 Sys: 1 Dp: 4690 El: 6 : 0..0)
WCCOAui7:	     2: 35
WCCOAui7:	     3: 35
WCCOAui7:     4: dyn_anytype 3 items
WCCOAui7:	     1: NSP2_P:AlarmCounter.status.Typ_Alert (Type: 1321 Sys: 1 Dp: 4690 El: 6 : 0..0)
WCCOAui7:	     2: 7
WCCOAui7:	     3: 7
WCCOAui7:]
The fist query clearly shows the 3 values that I have and their timestamp.
The second query returns average when I use queryRDBdirect=1 but it doesn't group by 1-minute intervals.
The second query returns god knows what when I use queryRDBdirect=0

Am I missing smth obvious? How is this supposed to work? Any ideas/experience?

Post Reply
1 post • Page 1 of 1