[Info-ingres] Listagg.....

[Adrian Williamson]

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…

 

[Martin Bowes]

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

[Adrian Williamson]

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

[Martin Bowes]

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

 

[Adrian Williamson]

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...@ndph.ox.ac.uk>
Sent: 12 February 2021 08:56
To: Adrian Williamson <adrian.w...@rationalcommerce.com>; 'Ingres and related product discussion forum' <info-...@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.

[Michael Leo]

And this is why I'm still on this list 3 years out of using Ingres.

Marty and Roy crack me up!  (Karl, you are funny too ... just not as funny.)

Michael Leo

Kettle River Consulting Inc

612-859-2108

ml...@kettleriverconsulting.com

---

From: info-ingr...@lists.planetingres.org <info-ingr...@lists.planetingres.org> on behalf of Martin Bowes <martin...@ndph.ox.ac.uk>
Sent: Friday, February 12, 2021 2:56 AM

To: Adrian Williamson <adrian.w...@rationalcommerce.com>; 'Ingres and related product discussion forum' <info-...@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.

[Karl Schendel]

> Adrian Williamson <adrian.w...@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

[Adrian Williamson]

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-ingr...@lists.planetingres.org <info-ingr...@lists.planetingres.org> On Behalf Of Karl Schendel
Sent: 12 February 2021 16:36
To: Adrian Williamson <adrian.w...@rationalcommerce.com>; Ingres and related product discussion forum <info-...@lists.planetingres.org>
Subject: Re: [Info-ingres] Listagg.....

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

[Karl Schendel]

> On Feb 12, 2021, at 12:03 PM, Adrian Williamson <adrian.w...@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.