Forum: >>> Magnum BBS <<<

[Info-ingres] Listagg.....

From Adrian Williamson@21:1/5 to Ingres and related product discussi on Thu Feb 11 15:32:00 2021

This is a multipart message in MIME format.

String Aggregate Functions

Note: Valid for X100 tables only. – FECKERS.

String aggregate functions concatenate a set of string values.

LISTAGG

If I paid a licence fee I’d be upset…

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta
http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style></head><body lang=EN-GB link=blue vlink=purple><div class=WordSection1><div><div>String Aggregate Functions<o:p></o:p>Note:  Valid for X100 tables only. – FECKERS.<

</o:p>String aggregate functions concatenate a set of string values.<o:p></o:p>LISTAGG<o:p></o:p><o:p> </o:p>If I paid a licence fee I’d be upset…

<o:p></o:p><o:p> </o:p></div></div></div></body></html>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Adrian Williamson@21:1/5 to Martin Bowes on Fri Feb 12 08:40:09 2021

To: info-ingres@lists.planetingres.org (Ingres and related product discussion forum)

This is a multipart message in MIME format.

Hey Marty,

Thanks for pointing that out.

Yes well, this customer is restrained to 11.0 by the prohibitive capital costs of testing one version of Ingres to the next.

It also appears that the licence fees are not being spent on maintaining the online documentation repository, as there is no sperate 11.1 section, or should I be looking at Actian X.11.1?

Much to my disgust I found myself looking at the database procedure syntax again and shortly after, wondering why ‘line 3’ was such a problem for the parsing of the procedure during the create process.

No matter how I edited the body.

I think it is 20 years since I had to write a DB procedure from scratch, it reminded me that:

1. There is no chapter called ‘Working with DB Procedures’
2. Emma still has not delivered on making OpenROAD the database language (she said this about 20 years ago at a conference)

All working now.

How is Oxford anyway?

Must be a quiet without all that foreign money being launder around the streets.

Cheers

Adrian

From: Martin Bowes <martin.bowes@ndph.ox.ac.uk>
Sent: 12 February 2021 08:10
To: Adrian Williamson <adrian.williamson@rationalcommerce.com>; 'Ingres and related product discussion forum' <info-ingres@lists.planetingres.org>
Subject: RE: [Info-ingres] Listagg.....

Feck ye not Adrian,

LISTAGG is available to ordinary Ingres tables from 11.1.

Here is an example, largely stolen from the 11.1 SQL Reference guide…

declare global temporary table employee(

dept varchar(20),

empno integer4,

firstname varchar(20),

lastname varchar(20)

);

insert into employee values

('Marketing', 101, 'Douglas', 'Cray'),

('Marketing', 103, 'Dong', 'Luang'),

('Marketing', 105, 'Dennis', 'Indolay'),

('Admin', 107, 'Sherry', 'Keller'),

('Admin', 109, 'Carl', 'Nader'),

('Admin', 111, 'Ruth', 'Turret'),

('Sales', 113, 'Andrew', 'Bonnet'),

('Sales', 115, 'Oscar', 'Hender'),

('Sales', 117, 'Justin', 'Braushere'),

('Sales', 119, '', 'Smith');

/* Concatenate the names of all employees, ordered by last name (LISTAGG used

** as a simple aggregate):

*/

SELECT LISTAGG(lastname)

WITHIN GROUP (ORDER BY lastname) AS PERSONNEL

FROM employee;

Will return: BonnetBraushereCrayHenderIndolayKellerLuangNaderSmithTurret

So my Licence fee angst is not perturbed by this at least.

Marty

From: Adrian Williamson <adrian.williamson@rationalcommerce.com <mailto:adrian.williamson@rationalcommerce.com> >
Sent: 11 February 2021 15:32
To: 'Ingres and related product discussion forum' <info-ingres@lists.planetingres.org <mailto:info-ingres@lists.planetingres.org> >
Subject: [Info-ingres] Listagg.....

String Aggregate Functions

Note: Valid for X100 tables only. – FECKERS.

String aggregate functions concatenate a set of string values.

LISTAGG

If I paid a licence fee I’d be upset…

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta
http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style></head><body lang=EN-GB link=blue vlink=purple><div class=WordSection1>Hey Marty,<o:p></o:p> </o:p>Thanks for pointing that out.<o:p></o:p><o:p> </o:p>Yes well, this customer is restrained to 11.0 by the prohibitive capital costs of testing one version of Ingres to the next.<o:p></o:p><o:p>&
nbsp;</o:p>It also appears that the licence fees are not being spent on maintaining the online documentation repository, as there is no sperate 11.1 section, or should I be looking at
Actian X.11.1?<o:p></o:p><o:p> </o:p>Much to my disgust I found myself looking at the database procedure
syntax again and shortly after, wondering why ‘line 3’ was such a problem for the parsing of the procedure during the create process.<o:p></o:p><o:p> </o:p>No matter how I edited the body.<o:p></o:p><o:p> </o:p>I think it is 20 years since I had to write a DB procedure from scratch, it reminded me that:<o:p></o:p><o:p> </o:p><ol style='margin-top:0cm' start=1 type=1><
li class=MsoListParagraph style='margin-left:0cm;mso-list:l0 level1 lfo1'>There is no chapter called ‘Working with DB Procedures’<o:p></o:p></li><li class=MsoListParagraph style='margin-left:0cm;mso-
list:l0 level1 lfo1'>Emma still has not delivered on making OpenROAD the database language (she said this about 20 years ago at a conference)<o:p></o:p></li></ol><o:p> </o:p>All working now.<o:p></o:p><o:p> </o:p>How is Oxford anyway?<o:p></o:p><o:p> </o:p>Must be a quiet without all
that foreign money being launder around the streets.<o:p></o:p><o:p> </o:p>Cheers<o:p></o:p><o:p> </o:p>Adrian<o:p></o:p><o:p> </o:p><o:p> </o:p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'>From: Sent: 12 February 2021 08:10 To: Adrian Williamson <adrian.williamson@rationalcommerce.com>; 'Ingres and related product discussion forum' <info-ingres@lists.planetingres.

org> Subject: RE: [Info-ingres] Listagg.....<o:p></o:p></div></div><o:p> </o:p>Feck ye not Adrian,<o:p></o:p><o:p> </o:p>LISTAGG is available to ordinary Ingres tables from 11.1.<o:p></o:p><o:p> </o:p>Here is an example, largely stolen from the 11.1 SQL Reference guide…<o:p></o:p><o:p> </o:p>declare global temporary table
employee(<o:p></o:p>        dept varchar(20),<o:p></o:p>        empno integer4,<o:p></o:p>        firstname varchar(20),<o:p></o:p>       
lastname  varchar(20)<o:p></o:p>);<o:p></o:p><o:p> </o:p>insert into employee values<o:p></o:p>    ('Marketing', 101, 'Douglas', 'Cray'),<o:p></o:p>    ('Marketing', 103, 'Dong', 'Luang'),<o:p></o:p>    ('Marketing', 105, 'Dennis', 'Indolay'),<

</o:p>    ('Admin', 107, 'Sherry', 'Keller'),<o:p></o:p>    ('Admin', 109, 'Carl', 'Nader'),<o:p></o:p>  &
nbsp; ('Admin', 111, 'Ruth', 'Turret'),<o:p></o:p>    ('Sales', 113, 'Andrew', 'Bonnet'),<o:p></o:p>    ('Sales', 115, 'Oscar', 'Hender'),<o:p></o:p>    ('Sales', 117, 'Justin', 'Braushere'),<o:p></o:p>    ('Sales', 119, '', '
Smith');<o:p></o:p><o:p> </o:p>/* Concatenate the names of all employees, ordered by last name (LISTAGG used<o:p></o:p>** as a simple aggregate):<o:p><
/o:p>*/<o:p></o:p>
SELECT LISTAGG(lastname)<o:p></o:p>    WITHIN GROUP (ORDER BY lastname) AS PERSONNEL<o:p></o:p>FROM employee;<o:p></o:p><o:p> </o:p>
Will return: BonnetBraushereCrayHenderIndolayKellerLuangNaderSmithTurret</o:p><o:p> </o:p>So my Licence fee angst is not perturbed by this at

least.<o:p></o:p><o:p> </o:p>Marty<o:p></o:p><o:p> </o:p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'>From: Adrian
Williamson <<a href="mailto:adrian.williamson@rationalcommerce.com">adrian.williamson@rationalcommerce.com</a>> Sent: 11 February 2021 15:32 To: 'Ingres and related product discussion forum' <<a href="mailto:info-ingres@
lists.planetingres.org">info-ingres@lists.planetingres.org</a>> Subject: [Info-ingres] Listagg.....<o:p></o:p></div></div><o:p> </o:p><div><div>String Aggregate Functions<o:p></o:p></

Note:  Valid for X100 tables only. – FECKERS.<o:p></o:p>String aggregate functions concatenate a set of string values.<o:p></o:p>LISTAGG<o:p></o:p><o:p> </o:p>If I paid a licence fee I’d be upset…<o:p></o:p><o:p> </o:p></div></div></div></body></html>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Adrian Williamson@21:1/5 to Martin Bowes on Fri Feb 12 09:15:44 2021

To: info-ingres@lists.planetingres.org (Ingres and related product discussion forum)

This is a multipart message in MIME format.

Oh, must be snow blind two of us missed that chapter 7 yesterday.

Thanks for semicolon tip.

I know where to come to get wee DB Procs written in the future me thinks.

You like to be paid in pints, pies or both?

EOL Ingres 11.0 January 31, 2023 – something to worry about next year.

Given that 11.1 stops April 2023 skipping to the latest seems prudent.

Mmm the support windows do seem to be shrinking, I see OR is on a two year cycle now down from seven.

Yes I’ve heard about Bournemouth, used to pass it when fleeing the country to the mainland.

Cheers

Adrian

From: Martin Bowes <martin.bowes@ndph.ox.ac.uk>
Sent: 12 February 2021 08:56
To: Adrian Williamson <adrian.williamson@rationalcommerce.com>; 'Ingres and related product discussion forum' <info-ingres@lists.planetingres.org>
Subject: RE: [Info-ingres] Listagg.....

Hi Adrian,

* Yes well, this customer is restrained to 11.0 by the prohibitive capital costs of testing one version of Ingres to the next.

Hate to point out to them that 11.2 is just around the corner, end of May I believe. After that 11.0 will face end of support.

* It also appears that the licence fees are not being spent on maintaining the online documentation repository, as there is no sperate 11.1 section, or should I be looking at Actian X.11.1?

Correct.

* Much to my disgust I found myself looking at the database procedure syntax again and shortly after, wondering why ‘line 3’ was such a problem for the parsing of the procedure during the create process.

Count the semicolons, or do a copydb –with_proc to extract the procedures. The latter will put comments indicating the line number into the code.

* I think it is 20 years since I had to write a DB procedure from scratch, it reminded me that:

1. There is no chapter called ‘Working with DB Procedures’

The SQL reference guide has chapter 7: Understanding Database Procedures…

2. Emma still has not delivered on making OpenROAD the database language (she said this about 20 years ago at a conference)

I’ll let you take that up with Aunty Em. Frankly she scares me too much.

* How is Oxford anyway? Must be a quiet without all that foreign money being launder around the streets.

I’m sheltering in Sunny Bournemouth. And we have plenty of people loitering with ill intent around here, even in Winter.

Marty

<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta
http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style></head><body lang=EN-GB link=blue vlink=purple><div class=WordSection1>Oh, must be snow blind two of us missed that chapter 7 yesterday.<o:p></o:p><o:p> </o:p>Thanks for semicolon tip.<o:p></o:p><o:

 </o:p>I know where to come to get wee DB Procs written in the future me thinks.<o:p></o:p><o:p> </o:
You like to be paid in pints, pies or both?<o:p></o:p><o:p> </o:p>EOL Ingres 11.0 January 31, 2023 – something to worry about next year.<o:p></o:p><o:p> </o:p>Given that 11.1 stops April 2023 skipping to the latest seems
prudent.<o:p></o:p><o:p> </o:p>Mmm the support windows do seem to be shrinking, I see OR is on a two year cycle now down from seven.<o:p></o:p><o:p> </o:p>Yes I’ve heard about Bournemouth, used to pass it when fleeing the country to the mainland.<o:p></o:p><o:p> </o:p>Cheers<o:p></o:p><o:p> </o:p>Adrian<o:p></o:p><o:p> </o:p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'>From: Martin Bowes <martin.
bowes@ndph.ox.ac.uk> Sent: 12 February 2021 08:56 To: Adrian Williamson <adrian.williamson@rationalcommerce.com>; 'Ingres and related product discussion forum' <info-ingres@lists.planetingres.org> Subject: RE:
[Info-ingres] Listagg.....<o:p></o:p></div></div><o:p> </o:p>Hi Adrian,<o:p></o:p><o:p> </o:p><ul style='margin-top:0cm' type=disc><li class=MsoListParagraph style='margin-left:0cm;mso-list:l0 level1 lfo2'>Yes well, this customer is restrained to 11.0
by the prohibitive capital costs of testing one version of Ingres to the next.<o:p></o:p></li></ul>Hate to point out to them that 11.2 is just
around the corner, end of May I believe. After that 11.0 will face end of support.<o:p></o:p><o:p> </o:p><ul style='margin-top:0cm' type=disc><li class=
MsoListParagraph style='margin-left:0cm;mso-list:l0 level1 lfo2'>It also appears that the licence fees are not being spent on maintaining the online documentation repository, as there is no sperate 11.1 section,
or should I be looking at Actian X.11.1?<o:p></o:p></li></ul>Correct.<o:p></o:p><o:p> </o:p><ul style='margin-top:0cm' type=disc><li class=MsoListParagraph style='margin-left:0cm;mso-list:l0 level1 lfo2'>Much to my disgust I
found myself looking at  the database procedure syntax again and shortly after, wondering why ‘line 3’ was such a problem for the parsing of the procedure during the create process.<o:p></o:p></li></ul>Count the semicolons, or do a copydb –with_proc to extract the procedures. The latter will put comments indicating the line number into the code.<o:p></o:p><o:p> </o:p><ul style='margin-top:0cm' type=disc><li class=MsoListParagraph style='margin-left:0cm;mso-list:l0 level1 lfo2'>I think
it is 20 years since I had to write a DB procedure from scratch, it reminded me that:<o:p></o:p></li></ul><ol style='margin-top:0cm' start=1 type=1><li class=MsoNormal style='margin-left:18.0pt;mso-list:l1 level1 lfo4'>There is no chapter called ‘Working with DB Procedures’<o:p></o:p></li></ol>The SQL reference guide has chapter 7: 
Understanding Database Procedures…<o:p></o:p><o:p> </o:p><ol style='margin-top:0cm' start=2 type=1><li class=MsoNormal style=
'margin-left:18.0pt;mso-list:l1 level1 lfo4'>Emma still has not delivered on making OpenROAD the database language (she said this about 20 years ago at a conference)<o:p></o:p></li></ol>I’ll let you take that up with Aunty Em. Frankly she scares me too much.<o:p></o:p><o:

 </o:p><ul style='margin-top:0cm' type=disc><li class=MsoListParagraph style='color:#1F497D;margin-left:0cm;mso-list:l0 level1 lfo2'>How is Oxford anyway? Must be a quiet without all that foreign money being launder around the streets.<o:p></o:p></li></ul>I’m sheltering in Sunny Bournemouth. And we have plenty of people loitering with ill intent around here, even in Winter.<o:p></o:p>
<o:p> </o:p><div><div>Marty<o:p></o:p><o:p> </o:p></div></div></div></body></html>

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Karl Schendel@21:1/5 to Adrian Williamson on Fri Feb 12 12:08:54 2021

To: info-ingres@lists.planetingres.org (Ingres and related product discussion forum)

On Feb 12, 2021, at 12:03 PM, Adrian Williamson <adrian.williamson@rationalcommerce.com> wrote:

I think that was the afternoon session of the IUG-UK - were you all on the sauce* at lunch?

Tut Tut.

I'm thinking of one of the big engineering conferences. I guess the enthusiasm carried forward
a bit. We may have talked ourselves into thinking it was a thing. (It's still not a terrible idea, in
theory at least.)

As for "sauce at lunch", we're a global company. "What is lunch?", asked Pilate.

I was still in my early thirties back then, don’t recall a lot of detail, I may have headed home on the bike after the conference.

Sauce = Liquor = Sauce

-----Original Message-----
From: info-ingres-bounces@lists.planetingres.org <info-ingres-bounces@lists.planetingres.org> On Behalf Of Karl Schendel
Sent: 12 February 2021 16:36
To: Adrian Williamson <adrian.williamson@rationalcommerce.com>; Ingres and related product discussion forum <info-ingres@lists.planetingres.org>
Subject: Re: [Info-ingres] Listagg.....

Adrian Williamson <adrian.williamson@rationalcommerce.com> wrote:

• Emma still has not delivered on making OpenROAD the database
language (she said this about 20 years ago at a conference)

Tell you what, you supply Engineering wth the amount of liquor that was being consumed when that statement was made, we'll see what we can do!

I remember that discussion, which is remarkable because even the next day, I didn't remember much else from that evening...

Karl

_______________________________________________
Info-ingres mailing list
Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Karl Schendel@21:1/5 to Adrian Williamson on Fri Feb 12 11:36:27 2021

To: info-ingres@lists.planetingres.org (Ingres and related product discussion forum)

Adrian Williamson <adrian.williamson@rationalcommerce.com> wrote:

• Emma still has not delivered on making OpenROAD the database language (she said this about 20 years ago at a conference)

Tell you what, you supply Engineering wth the amount of liquor that was being consumed when that
statement was made, we'll see what we can do!

I remember that discussion, which is remarkable because even the next day, I didn't remember
much else from that evening...

Karl

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

From Adrian Williamson@21:1/5 to Karl Schendel on Fri Feb 12 17:03:33 2021

To: info-ingres@lists.planetingres.org (Ingres and related product discussion forum)

I think that was the afternoon session of the IUG-UK - were you all on the sauce* at lunch?

Tut Tut.

I was still in my early thirties back then, don’t recall a lot of detail, I may have headed home on the bike after the conference.

Sauce = Liquor = Sauce

-----Original Message-----
From: info-ingres-bounces@lists.planetingres.org <info-ingres-bounces@lists.planetingres.org> On Behalf Of Karl Schendel
Sent: 12 February 2021 16:36
To: Adrian Williamson <adrian.williamson@rationalcommerce.com>; Ingres and related product discussion forum <info-ingres@lists.planetingres.org>
Subject: Re: [Info-ingres] Listagg.....

Adrian Williamson <adrian.williamson@rationalcommerce.com> wrote:

• Emma still has not delivered on making OpenROAD the database language (she said this about 20 years ago at a conference)

Tell you what, you supply Engineering wth the amount of liquor that was being consumed when that statement was made, we'll see what we can do!

I remember that discussion, which is remarkable because even the next day, I didn't remember much else from that evening...

Karl

_______________________________________________
Info-ingres mailing list
Info-ingres@lists.planetingres.org https://lists.planetingres.org/mailman/listinfo/info-ingres

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Who's Online
Recent Visitors
- Bob Worm
  Fri Apr 26 15:47:21 2024
  from Wales, Uk via Telnet
- Bob Worm
  Fri Apr 26 10:09:36 2024
  from Wales, Uk via Telnet
- Bob Worm
  Fri Apr 26 08:24:20 2024
  from Wales, Uk via Telnet
- Bob Worm
  Fri Apr 26 06:40:30 2024
  from Wales, Uk via Telnet

System Info

Sysop:	Keyop
Location:	Huddersfield, West Yorkshire, UK
Users:	296
Nodes:	16 (2 / 14)
Uptime:	80:41:57
Calls:	6,658
Calls today:	4
Files:	12,203
Messages:	5,333,189
Posted today:	1

[Info-ingres] Listagg.....

Who's Online

Recent Visitors

System Info