Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
[Info-Ingres] string aggregation function
66 views
Skip to first unread message
Martin Bowes
Jan 2, 2013, 6:52:30 AM1/2/13
to info-...@kettleriverconsulting.com
Hi All,
Happy new year and all that jazz.
And just to start things off for 2013...
I need a string aggregation function that can build a long varchar. I seem to recall that this was mooted for Vectorwise 3.0...although I would need this for standard Ingres as well. I think it was going to be called listagg.
Can anyone shed some light?
Martin Bowes
Happy new year and all that jazz.
And just to start things off for 2013...
I need a string aggregation function that can build a long varchar. I seem to recall that this was mooted for Vectorwise 3.0...although I would need this for standard Ingres as well. I think it was going to be called listagg.
Can anyone shed some light?
Martin Bowes
Martin Bowes
Jan 3, 2013, 6:01:13 AM1/3/13
to Ingres and related product discussion forum
Hi Kristoff,
Yep a varchar(2000) is not going to be much use to me in this case. I would expect a long varchar type would be more useful in general, so I'll check if there is any possibility of this being done.
Marty
-----Original Message-----
From: Kristoff [mailto:kristof...@ingres.com]
Sent: 03 January 2013 08:56
To: info-...@kettleriverconsulting.com
Cc: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] string aggregation function
Am Donnerstag, 3. Januar 2013 09:51:37 UTC+1 schrieb Kristoff:
>
>
>
> Kristoff
forgot to mention, it is an aggregate function, so concatenating multiple row values into one result (per group) _______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Yep a varchar(2000) is not going to be much use to me in this case. I would expect a long varchar type would be more useful in general, so I'll check if there is any possibility of this being done.
Marty
-----Original Message-----
From: Kristoff [mailto:kristof...@ingres.com]
Sent: 03 January 2013 08:56
To: info-...@kettleriverconsulting.com
Cc: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] string aggregation function
Am Donnerstag, 3. Januar 2013 09:51:37 UTC+1 schrieb Kristoff:
> Am Mittwoch, 2. Januar 2013 12:41:03 UTC+1 schrieb Martin Bowes:
>
> > Hi All,
>
> >
>
> >
>
> >
>
> > Happy new year and all that jazz.
>
> >
>
> >
>
> >
>
> > And just to start things off for 2013.
>
> >
>
> >
>
> >
>
> > I need a string aggregation function that can build a long varchar. I seem to recall that this was mooted for Vectorwise 3.0.although I would need this for standard Ingres as well. I think it was going to be called listagg.
>
> > Hi All,
>
> >
>
> >
>
> >
>
> > Happy new year and all that jazz.
>
> >
>
> >
>
> >
>
> > And just to start things off for 2013.
>
> >
>
> >
>
> >
>
>
> >
>
> >
>
> >
>
> > Can anyone shed some light?
>
> >
>
> >
>
> >
>
> > Martin Bowes
>
>
>
> Yes, listagg is coming with Vectorwise 3.0, but there is no support for Ingres tables. Also it returns a varchar(2000), not a long varchar.
> >
>
> >
>
> >
>
> > Can anyone shed some light?
>
> >
>
> >
>
> >
>
> > Martin Bowes
>
>
>
>
>
>
> Kristoff
forgot to mention, it is an aggregate function, so concatenating multiple row values into one result (per group) _______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Ian Kirkham
Jan 3, 2013, 7:06:32 AM1/3/13
to Ingres and related product discussion forum
Hi Marty,
We've no plans to change the result type of LISTAGG to a LOBG object as the peer implementations are all satisfied with fixed width result.
As function instances define their result type based on the function name in combination with parameters, you'd need to define a parallel LISTAGG that took a LONG parameter. This ought to be doable but would probably perform poorly based on how the concatenation of LONGs is implemented - there is no notion of CONCAT accumulator to avoid the n-generated, ever increasing, long object temporaries. Not a small project.
Even adding a different named aggregate with a non-LONG parameter would not avoid to performance issues.
Regards,
Ian
We've no plans to change the result type of LISTAGG to a LOBG object as the peer implementations are all satisfied with fixed width result.
As function instances define their result type based on the function name in combination with parameters, you'd need to define a parallel LISTAGG that took a LONG parameter. This ought to be doable but would probably perform poorly based on how the concatenation of LONGs is implemented - there is no notion of CONCAT accumulator to avoid the n-generated, ever increasing, long object temporaries. Not a small project.
Even adding a different named aggregate with a non-LONG parameter would not avoid to performance issues.
Regards,
Ian
Roy Hann
Jan 3, 2013, 8:12:45 AM1/3/13
to info-...@kettleriverconsulting.com
Martin Bowes wrote:
> Hi All,
>
> Happy new year and all that jazz.
>
> And just to start things off for 2013...
> Hi All,
>
> Happy new year and all that jazz.
>
>
> I need a string aggregation function that can build a long varchar.
> I seem to recall that this was mooted for Vectorwise 3.0...although I
> I need a string aggregation function that can build a long varchar.
> would need this for standard Ingres as well. I think it was going to
> be called listagg.
This intrigues me. There will have to be more to it than just
> be called listagg.
concatenation because--with good reason--there is no way to specify
ordering in an aggregate.
If you can't know the aggregate result based just on the set of input
rows, and if the aggregate can vary at the whim of the implementation
or the physical layout of rows, or even just randomly and from time to
time, then testing (and reproducibility) will be a pig of a job.
--
Roy
UK Actian User Association Conference 2013 will be on Tuesday June 11. 2013.
The latest information is available from www.uk-iua.org.uk.
Karl Schendel
Jan 3, 2013, 8:32:16 AM1/3/13
to Ingres and related product discussion forum
On Jan 3, 2013, at 7:28 AM, Roy Hann wrote:
> Martin Bowes wrote:
>
>> Hi All,
>>
>> Happy new year and all that jazz.
>>
>> And just to start things off for 2013...
>>
>> I need a string aggregation function that can build a long varchar.
>> I seem to recall that this was mooted for Vectorwise 3.0...although I
>> would need this for standard Ingres as well. I think it was going to
>> be called listagg.
>
> This intrigues me. There will have to be more to it than just
> concatenation because--with good reason--there is no way to specify
> ordering in an aggregate.
The window clause can include an order by.
Karl
Roy Hann
Jan 3, 2013, 9:14:53 AM1/3/13
to info-...@kettleriverconsulting.com
Karl Schendel wrote:
>
> On Jan 3, 2013, at 7:28 AM, Roy Hann wrote:
>
>
> On Jan 3, 2013, at 7:28 AM, Roy Hann wrote:
>
>> Martin Bowes wrote:
>>
>>> Hi All,
>>>
>>> Happy new year and all that jazz.
>>>
>>> And just to start things off for 2013...
>>>
>>> I need a string aggregation function that can build a long varchar.
>>> I seem to recall that this was mooted for Vectorwise 3.0...although I
>>> would need this for standard Ingres as well. I think it was going to
>>> be called listagg.
>>
>> This intrigues me. There will have to be more to it than just
>> concatenation because--with good reason--there is no way to specify
>> ordering in an aggregate.
>
>>
>>> Hi All,
>>>
>>> Happy new year and all that jazz.
>>>
>>> And just to start things off for 2013...
>>>
>>> I need a string aggregation function that can build a long varchar.
>>> I seem to recall that this was mooted for Vectorwise 3.0...although I
>>> would need this for standard Ingres as well. I think it was going to
>>> be called listagg.
>>
>> This intrigues me. There will have to be more to it than just
>> concatenation because--with good reason--there is no way to specify
>> ordering in an aggregate.
>
> Indeed, and that's why listagg is implemented as a "window" function.
> The window clause can include an order by.
Indeed, as you say. But I think only Vectorwise supports window
> The window clause can include an order by.
functions. (I'd love to hear I'm wrong and that Ingres does or will
support them too.)
Martin Bowes
Jan 3, 2013, 9:21:28 AM1/3/13
to Ingres and related product discussion forum
Good save Karl, I'd forgotten about the lack of ordering in aggregation.
-----Original Message-----
From: Karl Schendel [mailto:sche...@kbcomputer.com]
Sent: 03 January 2013 13:23
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] string aggregation function
-----Original Message-----
From: Karl Schendel [mailto:sche...@kbcomputer.com]
Sent: 03 January 2013 13:23
To: Ingres and related product discussion forum
Subject: Re: [Info-Ingres] string aggregation function
Martin Bowes
Jan 3, 2013, 9:26:44 AM1/3/13
to Ingres and related product discussion forum
Yep, I had that suspicion.
I'm thinking that my best option is to take this outside the database, just extract the data to a file and use a program to do the rest.
FYI. What I'm trying to solve is a problem where the boss wants to know absolutely that the answers to a query run now are the same as one he runs at some future time...possibly many years later. The extended time frame means that I can't rely on table grants or other restrictions as they can be dynamic. My thought was we could aggregate the output and do an md5sum which can then be stored with the query in some table. At any point the boss likes we simply rerun the query, aggregate, md5sum and compare.
Marty
I'm thinking that my best option is to take this outside the database, just extract the data to a file and use a program to do the rest.
FYI. What I'm trying to solve is a problem where the boss wants to know absolutely that the answers to a query run now are the same as one he runs at some future time...possibly many years later. The extended time frame means that I can't rely on table grants or other restrictions as they can be dynamic. My thought was we could aggregate the output and do an md5sum which can then be stored with the query in some table. At any point the boss likes we simply rerun the query, aggregate, md5sum and compare.
Marty
Karl Schendel
Jan 3, 2013, 9:29:25 AM1/3/13
to Ingres and related product discussion forum
On Jan 3, 2013, at 8:39 AM, Roy Hann wrote:
> Karl Schendel wrote:
>> ...
>> Indeed, and that's why listagg is implemented as a "window" function.
>> The window clause can include an order by.
>
> Indeed, as you say. But I think only Vectorwise supports window
> functions. (I'd love to hear I'm wrong and that Ingres does or will
> support them too.)
No, unfortunately, you are not wrong. The Ingres execution side does
>> The window clause can include an order by.
>
> Indeed, as you say. But I think only Vectorwise supports window
> functions. (I'd love to hear I'm wrong and that Ingres does or will
> support them too.)
not support window functions.
There have been a few ideas for doing that batted around, but nothing
concrete, and without a serious business case it's unlikely that Ingres
side window functions will get done in the near future.
Karl
Patrick COURANT
Jan 4, 2013, 9:07:35 AM1/4/13
to Roy Hann
Martin,
Please accept my best wishes for the new year, with "all that jazz" and all what goes with it ...
For the moment, I am trying to get track of this chap, far in Northern countries, who wrote some time ago a book about Ingres.
Would you remember, by any chance, the reference of the book?
I will be very grateful.
Again, my best for 2013, the year when MySql fades away ....
Patrick
> Date: Thu, 3 Jan 2013 00:56:25 -0800
> From: kristof...@ingres.com
> To: comp.datab...@googlegroups.com
> CC: info-...@kettleriverconsulting.com
> >
> > >
> >
> > >
> >
> > >
> >
> > > I need a string aggregation function that can build a long varchar. I seem to recall that this was mooted for Vectorwise 3.0…although I would need this for standard Ingres as well. I think it was going to be called listagg.
Please accept my best wishes for the new year, with "all that jazz" and all what goes with it ...
For the moment, I am trying to get track of this chap, far in Northern countries, who wrote some time ago a book about Ingres.
Would you remember, by any chance, the reference of the book?
I will be very grateful.
Again, my best for 2013, the year when MySql fades away ....
Patrick
> Date: Thu, 3 Jan 2013 00:56:25 -0800
> From: kristof...@ingres.com
> To: comp.datab...@googlegroups.com
> CC: info-...@kettleriverconsulting.com
> Subject: Re: [Info-Ingres] string aggregation function
>
> Am Donnerstag, 3. Januar 2013 09:51:37 UTC+1 schrieb Kristoff:
> > Am Mittwoch, 2. Januar 2013 12:41:03 UTC+1 schrieb Martin Bowes:
> >
> > > Hi All,
> >
> > >
> >
> > >
> >
> > >
> >
> > > Happy new year and all that jazz.
> >
> > >
> >
> > >
> >
> > >
> >
> > > And just to start things off for 2013…
>
> Am Donnerstag, 3. Januar 2013 09:51:37 UTC+1 schrieb Kristoff:
> > Am Mittwoch, 2. Januar 2013 12:41:03 UTC+1 schrieb Martin Bowes:
> >
> > > Hi All,
> >
> > >
> >
> > >
> >
> > >
> >
> > > Happy new year and all that jazz.
> >
> > >
> >
> > >
> >
> > >
> >
> >
> > >
> >
> > >
> >
> > >
> >
> > > I need a string aggregation function that can build a long varchar. I seem to recall that this was mooted for Vectorwise 3.0…although I would need this for standard Ingres as well. I think it was going to be called listagg.
> >
> > >
> >
> > >
> >
> > >
> >
> > > Can anyone shed some light?
> >
> > >
> >
> > >
> >
> > >
> >
> > > Martin Bowes
> >
> >
> >
> > Yes, listagg is coming with Vectorwise 3.0, but there is no support for Ingres tables. Also it returns a varchar(2000), not a long varchar.
> >
> >
> >
> > Kristoff
>
> forgot to mention, it is an aggregate function, so concatenating multiple row values into one result (per group)
>
>
> > >
> >
> > >
> >
> > >
> >
> > > Can anyone shed some light?
> >
> > >
> >
> > >
> >
> > >
> >
> > > Martin Bowes
> >
> >
> >
> > Yes, listagg is coming with Vectorwise 3.0, but there is no support for Ingres tables. Also it returns a varchar(2000), not a long varchar.
> >
> >
> >
> > Kristoff
>
> forgot to mention, it is an aggregate function, so concatenating multiple row values into one result (per group)
>
>
Martin Bowes
Jan 4, 2013, 9:16:13 AM1/4/13
to Ingres and related product discussion forum
Do you mean 'The SQL guide to Ingres' by Rick van der Lans?
The ISBN is 978-0-557-07043-5.
Marty
From: Patrick COURANT [mailto:p6co...@hotmail.com]
Sent: 04 January 2013 13:54
To: Roy Hann
Subject: Re: [Info-Ingres] string aggregation function
Martin,
Please accept my best wishes for the new year, with "all that jazz" and all what goes with it ...
For the moment, I am trying to get track of this chap, far in Northern countries, who wrote some time ago a book about Ingres.
Would you remember, by any chance, the reference of the book?
I will be very grateful.
Again, my best for 2013, the year when MySql fades away ....
Patrick
> Date: Thu, 3 Jan 2013 00:56:25 -0800
> From: kristof...@ingres.com<mailto:kristof...@ingres.com>
> To: comp.datab...@googlegroups.com<mailto:comp.datab...@googlegroups.com>
> CC: info-...@kettleriverconsulting.com<mailto:info-...@kettleriverconsulting.com>
> >
> > >
> >
> > >
> >
> > >
> >
> > > I need a string aggregation function that can build a long varchar. I seem to recall that this was mooted for Vectorwise 3.0...although I would need this for standard Ingres as well. I think it was going to be called listagg.
> http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
The ISBN is 978-0-557-07043-5.
Marty
From: Patrick COURANT [mailto:p6co...@hotmail.com]
Sent: 04 January 2013 13:54
To: Roy Hann
Subject: Re: [Info-Ingres] string aggregation function
Martin,
Please accept my best wishes for the new year, with "all that jazz" and all what goes with it ...
For the moment, I am trying to get track of this chap, far in Northern countries, who wrote some time ago a book about Ingres.
Would you remember, by any chance, the reference of the book?
I will be very grateful.
Again, my best for 2013, the year when MySql fades away ....
Patrick
> Date: Thu, 3 Jan 2013 00:56:25 -0800
> To: comp.datab...@googlegroups.com<mailto:comp.datab...@googlegroups.com>
> CC: info-...@kettleriverconsulting.com<mailto:info-...@kettleriverconsulting.com>
> Subject: Re: [Info-Ingres] string aggregation function
>
> Am Donnerstag, 3. Januar 2013 09:51:37 UTC+1 schrieb Kristoff:
> > Am Mittwoch, 2. Januar 2013 12:41:03 UTC+1 schrieb Martin Bowes:
> >
> > > Hi All,
> >
> > >
> >
> > >
> >
> > >
> >
> > > Happy new year and all that jazz.
> >
> > >
> >
> > >
> >
> > >
> >
> > > And just to start things off for 2013...
>
> Am Donnerstag, 3. Januar 2013 09:51:37 UTC+1 schrieb Kristoff:
> > Am Mittwoch, 2. Januar 2013 12:41:03 UTC+1 schrieb Martin Bowes:
> >
> > > Hi All,
> >
> > >
> >
> > >
> >
> > >
> >
> > > Happy new year and all that jazz.
> >
> > >
> >
> > >
> >
> > >
> >
> >
> > >
> >
> > >
> >
> > >
> >
> > > I need a string aggregation function that can build a long varchar. I seem to recall that this was mooted for Vectorwise 3.0...although I would need this for standard Ingres as well. I think it was going to be called listagg.
> >
> > >
> >
> > >
> >
> > >
> >
> > > Can anyone shed some light?
> >
> > >
> >
> > >
> >
> > >
> >
> > > Martin Bowes
> >
> >
> >
> > Yes, listagg is coming with Vectorwise 3.0, but there is no support for Ingres tables. Also it returns a varchar(2000), not a long varchar.
> >
> >
> >
> > Kristoff
>
> forgot to mention, it is an aggregate function, so concatenating multiple row values into one result (per group)
>
>
> _______________________________________________
> Info-Ingres mailing list
> Info-...@kettleriverconsulting.com<mailto:Info-...@kettleriverconsulting.com>
> > >
> >
> > >
> >
> > >
> >
> > > Can anyone shed some light?
> >
> > >
> >
> > >
> >
> > >
> >
> > > Martin Bowes
> >
> >
> >
> > Yes, listagg is coming with Vectorwise 3.0, but there is no support for Ingres tables. Also it returns a varchar(2000), not a long varchar.
> >
> >
> >
> > Kristoff
>
> forgot to mention, it is an aggregate function, so concatenating multiple row values into one result (per group)
>
>
> _______________________________________________
> Info-Ingres mailing list
> http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Roy Hann
Jan 4, 2013, 9:43:43 AM1/4/13
to info-...@kettleriverconsulting.com
Patrick COURANT wrote:
> Martin,
>
> Please accept my best wishes for the new year, with "all that jazz" and all what goes with it ...
>
> For the moment, I am trying to get track of this chap, far in Northern countries, who wrote some time ago a book about Ingres.
>
> Would you remember, by any chance, the reference of the book?
Are you thinking of Rick van der Lans and his *SQL Guide to Ingres*?
> Martin,
>
> Please accept my best wishes for the new year, with "all that jazz" and all what goes with it ...
>
> For the moment, I am trying to get track of this chap, far in Northern countries, who wrote some time ago a book about Ingres.
>
> Would you remember, by any chance, the reference of the book?
http://www.amazon.co.uk/The-Guide-Ingres-Rick-Lans/dp/0557070430
ISBN-10: 0557070430
ISBN-13: 978-0557070435
--
Roy
UK Actian User Association Conference 2013 will be on Tuesday June 11. 2013.
The latest information is available from www.uk-iua.org.uk.
_______________________________________________
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
http://ext-cando.kettleriverconsulting.com/mailman/listinfo/info-ingres
Info-Ingres mailing list
Info-...@kettleriverconsulting.com
Patrick COURANT
Jan 6, 2013, 5:44:05 PM1/6/13
to Roy Hann
Yes that's the book.
A thousand thanks, Roy.
Again my best for the year 2013.
Patrick
> From: spec...@processed.almost.meat
> Date: Fri, 4 Jan 2013 14:02:12 +0000
> To: info-...@kettleriverconsulting.com
> Subject: Re: [Info-Ingres] string aggregation function
>
A thousand thanks, Roy.
Again my best for the year 2013.
Patrick
> From: spec...@processed.almost.meat
> Date: Fri, 4 Jan 2013 14:02:12 +0000
> To: info-...@kettleriverconsulting.com
> Subject: Re: [Info-Ingres] string aggregation function
>
Patrick COURANT
Jan 9, 2013, 6:04:30 AM1/9/13
to Ingres and related product discussion forum
Yes, that's it.
Thank you so much, and again my best for the New Year.
-----Message d'origine-----
De : info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] De la part de Roy
Hann
Envoyé : vendredi 4 janvier 2013 15:02
À : info-...@kettleriverconsulting.com
Objet : Re: [Info-Ingres] string aggregation function
Thank you so much, and again my best for the New Year.
-----Message d'origine-----
De : info-ingr...@kettleriverconsulting.com
[mailto:info-ingr...@kettleriverconsulting.com] De la part de Roy
Hann
Envoyé : vendredi 4 janvier 2013 15:02
À : info-...@kettleriverconsulting.com
Objet : Re: [Info-Ingres] string aggregation function
0 new messages