Timeseries AggregateRange
Steven wanted the ROW value returned by the TimeSeries AggregateRange() function displayed as normal columns (tabular form) on IDS 12.1.xC1. His attempts using TABLE(MULTISET(...)) and TABLE((select ...)) derived tables all failed. Suggestions to use transpose() (which applies to AggregateBy, returning a timeseries) didn't help. Mark Ashworth posted a working example: wrap the query in a plain derived table with an alias and column name, e.g. select result.minmax.* from (select AggregateRange(...)::row_type from tab) as result(minmax). Steven confirmed the issue was the TABLE keyword — removing it made it work.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
IDS 12.1.xc1
When using the AggregateRange function, it returns a row (as noted in the
documentation).
However, I would like to return in tabular format.
It appears that this does not work. It looks as if the return value of
AggregateRange is not a row.
e.g.
select AggregateRange('AVG($pa), AVG($pb), AVG($pc)', raw_data, 0, '2012-01-12
00:00:00','2013-10-03 23:45:00')::three_val
from meter where id=123
Tabular Format
--------------
I've tried:
select * from table (multiset(select AggregateRange('AVG($pa), AVG($pb), AVG($pc)', raw_data, 0, '2012-01-12
00:00:00','2013-10-03 23:45:00')::three_val
from meter where id=123)) as vtab
AND
select * from table (multiset(select AggregateRange('AVG($pa), AVG($pb), AVG($pc)', raw_data, 0, '2012-01-12
00:00:00','2013-10-03 23:45:00')::three_val
from meter where id=123)) as vtab(mr)
AND
select * from table ((select AggregateRange('AVG($pa), AVG($pb), AVG($pc)',
raw_data, 0, '2012-01-12 00:00','2013-10-03 23:45')::three_val from meter
where id=123)) as tab(mr)
Any help appreciated
Regards
Steven
Try the transpose function
Cheers
Paul
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> STEVEN WEISS
> Sent: Friday, October 04, 2013 8:22 AM
> To: ids@iiug.org
> Subject: Timeseries AggregateRange [31600]
>
> IDS 12.1.xc1
>
> When using the AggregateRange function, it returns a row (as noted in the
> documentation).
>
> However, I would like to return in tabular format.
> It appears that this does not work. It looks as if the return value of
> AggregateRange is not a row.
>
> e.g.
>
> select AggregateRange('AVG($pa), AVG($pb), AVG($pc)', raw_data, 0, '2012-
> 01-12
> 00:00:00','2013-10-03 23:45:00')::three_val
> from meter where id=123
>
> Tabular Format
> --------------
> I've tried:
>
> select * from table (multiset(> select AggregateRange('AVG($pa), AVG($pb), AVG($pc)', raw_data, 0, '2012-
> 01-12
> 00:00:00','2013-10-03 23:45:00')::three_val
> from meter where id=123)) as vtab
>
> AND
>
> select * from table (multiset(> select AggregateRange('AVG($pa), AVG($pb), AVG($pc)', raw_data, 0, '2012-
> 01-12
> 00:00:00','2013-10-03 23:45:00')::three_val
> from meter where id=123)) as vtab(mr)
>
> AND
>
> select * from table ((select AggregateRange('AVG($pa), AVG($pb),
> AVG($pc)',
> raw_data, 0, '2012-01-12 00:00','2013-10-03 23:45')::three_val from meter
> where id=123)) as tab(mr)>
> Any help appreciated
>
> Regards
> Steven
>
>
> **********************************************************
> *********************
> Forum Note: Use "Reply" to post a response in the discussion forum.
I don't think this is it Transpose expects a timeseries to be returned (as with function AggregateBy). AggregateRange returns a row.
Hi Steven,
Have you tried replacing the * in the column list with the tab.mr.*:
for example:
select tab.mr.* from table ((select AggregateRange('AVG($pa), AVG($pb),
AVG($pc)',
raw_data, 0, '2012-01-12 00:00','2013-10-03 23:45')::three_val from meter
where id=123)) as tab(mr)
-- Mark.
Mark Ashworth
IBM Informix Extensibility Architect
Office phone: +1 (905) 413-5033
Alternate: +1 (905) 697-8094
Email: ashworth@ca.ibm.com
Check out my blog
From: "STEVEN WEISS" <sweiss@iafrica.com>
To: ids@iiug.org,
Date: 10/04/2013 09:23 AM
Subject: Timeseries AggregateRange [31600]
Sent by: ids-bounces@iiug.org
IDS 12.1.xc1
When using the AggregateRange function, it returns a row (as noted in the
documentation).
However, I would like to return in tabular format.
It appears that this does not work. It looks as if the return value of
AggregateRange is not a row.
e.g.
select AggregateRange('AVG($pa), AVG($pb), AVG($pc)', raw_data, 0,
'2012-01-12
00:00:00','2013-10-03 23:45:00')::three_val
from meter where id=123
Tabular Format
--------------
I've tried:
select * from table (multiset(
select AggregateRange('AVG($pa), AVG($pb), AVG($pc)', raw_data, 0,'2012-01-12
00:00:00','2013-10-03 23:45:00')::three_val
from meter where id=123)) as vtab
AND
select * from table (multiset(
select AggregateRange('AVG($pa), AVG($pb), AVG($pc)', raw_data, 0,'2012-01-12
00:00:00','2013-10-03 23:45:00')::three_val
from meter where id=123)) as vtab(mr)
AND
select * from table ((select AggregateRange('AVG($pa), AVG($pb),
AVG($pc)',
raw_data, 0, '2012-01-12 00:00','2013-10-03 23:45')::three_val from meter
where id=123)) as tab(mr)
Any help appreciated
Regards
Steven
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Mark, Tried that - no luck. It looks like the row that gets returned by AggregateRange is not being seen as a row. If I run a similar query using transpose(select(AggregateBy..... then I also get a rows returned and when wrapping this with a derived table, I get the correct result i.e. tabular format. I just cannot understand why the same won't work with AggregateRange which returns a row. Regards Steven
Hi Steven,
Here is what I just tried this with my own setup. First I retrieve the
results a the row values:
select first 3 AggregateRange('max($value),min($value)', readings, 0,
'2010-01-01 00:00:00.00000'::datetime year to fraction(5),
'2010-01-02 00:00:00.00000'::datetime year to fraction(5)
)::max_min_t from sm;
(expression) ROW('2010-01-01 00:00:00.00000',9556.19400 ,211.16900 )
(expression) ROW('2010-01-01 00:00:00.00000',9929.05100 ,516.97800 )
(expression) ROW('2010-01-01 00:00:00.00000',9914.53600 ,8.86100
)
3 row(s) retrieved.
I modified it as follows to get the results out in tabular form:
select first 3 result.minmax.*
from (select AggregateRange('max($value),min($value)', readings, 0,
'2010-01-01 00:00:00.00000'::datetime year to
fraction(5),
'2010-01-02 00:00:00.00000'::datetime year to
fraction(5)
)::max_min_t from sm) as result(minmax)
;
tstamp max min
2010-01-01 00:00:00.00000 9556.19400 211.16900
2010-01-01 00:00:00.00000 9929.05100 516.97800
2010-01-01 00:00:00.00000 9914.53600 8.86100
3 row(s) retrieved.
I hope this example helps.
-- Mark.
Mark Ashworth
IBM Informix Extensibility Architect
Office phone: +1 (905) 413-5033
Alternate: +1 (905) 697-8094
Email: ashworth@ca.ibm.com
Check out my blog
From: "STEVEN WEISS" <sweiss@iafrica.com>
To: ids@iiug.org,
Date: 10/04/2013 01:51 PM
Subject: Re: Timeseries AggregateRange [31614]
Sent by: ids-bounces@iiug.org
Hi Mark,
Tried that - no luck.
It looks like the row that gets returned by AggregateRange is not being
seen
as a row.
If I run a similar query using transpose(select(AggregateBy.....
then I also get a rows returned and when wrapping this with a derived
table, I
get the correct result i.e. tabular format.
I just cannot understand why the same won't work with AggregateRange which
returns a row.
Regards
Steven
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Mark, Problem was the TABLE keyword in the derived table. If I take this out, it works as expected. I understood from documentation that this needed to be in place in order to see a row in tabular format. Regards Steven