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><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Lucida Console";
panose-1:2 11 6 9 4 5 4 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
p.MsoListParagraph, li.MsoListParagraph, div.MsoListParagraph
{mso-style-priority:34;
margin-top:0cm;
margin-right:0cm;
margin-bottom:0cm;
margin-left:36.0pt;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
span.EmailStyle21
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
/* List Definitions */
@list l0
{mso-list-id:2012171351;
mso-list-type:hybrid;
mso-list-template-ids:-1541113874 134807567 134807577 134807579 134807567 134807577 134807579 134807567 134807577 134807579;}
@list l0:level1
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level2
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level3
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level4
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level5
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level6
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
@list l0:level7
{mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level8
{mso-level-number-format:alpha-lower;
mso-level-tab-stop:none;
mso-level-number-position:left;
text-indent:-18.0pt;}
@list l0:level9
{mso-level-number-format:roman-lower;
mso-level-tab-stop:none;
mso-level-number-position:right;
text-indent:-9.0pt;}
ol
{margin-bottom:0cm;}
ul
{margin-bottom:0cm;}
</style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" /> </o:shapelayout></xml><![endif]--></head><body lang=EN-GB link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Hey Marty,<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-
<o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Thanks for pointing that out.<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><
span style='mso-fareast-language:EN-US'>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></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p>&
nbsp;</o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>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></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>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></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p
class=MsoNormal><span style='mso-fareast-language:EN-US'>No matter how I edited the body.<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:
EN-US'>I think it is 20 years since I had to write a DB procedure from scratch, it reminded me that:<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><ol style='margin-top:0cm' start=1 type=1><
li class=MsoListParagraph style='margin-left:0cm;mso-list:l0 level1 lfo1'><span style='mso-fareast-language:EN-US'>There is no chapter called ‘Working with DB Procedures’<o:p></o:p></span></li><li class=MsoListParagraph style='margin-left:0cm;mso-
list:l0 level1 lfo1'><span style='mso-fareast-language:EN-US'>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></span></li></ol><p class=MsoNormal><span style='mso-fareast-
language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>All working now.<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span
style='mso-fareast-language:EN-US'>How is Oxford anyway?<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Must be a quiet without all
that foreign money being launder around the streets.<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Cheers<o:p></o:p></span></p><p
class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'>Adrian<o:p></o:p></span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></
span></p><p class=MsoNormal><span style='mso-fareast-language:EN-US'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US>From:</span></b><span lang=EN-
Martin Bowes <martin.bowes@ndph.ox.ac.uk> <br><b>Sent:</b> 12 February 2021 08:10<br><b>To:</b> Adrian Williamson <adrian.williamson@rationalcommerce.com>; 'Ingres and related product discussion forum' <info-ingres@lists.planetingres.
org><br><b>Subject:</b> RE: [Info-ingres] Listagg.....<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><p class=MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'>Feck ye not Adrian,<o:p></o:p></span></p><p class=
MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'>LISTAGG is available to ordinary Ingres tables from 11.1.<o:p></o:p></span></p><p class=
MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'>Here is an example, largely stolen from the 11.1 SQL Reference guide…<o:p></o:p></
span></p><p class=MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>declare global temporary table
employee(<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> dept varchar(20),<o:p></o:p></span></p><p class=MsoNormal style='text-
autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> empno integer4,<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida
Console"'> firstname varchar(20),<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>
lastname varchar(20)<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>);<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.
0pt;font-family:"Lucida Console"'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>insert into employee values<o:p></o:p></span></p><p class=MsoNormal style='text-
autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> ('Marketing', 101, 'Douglas', 'Cray'),<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida
Console"'> ('Marketing', 103, 'Dong', 'Luang'),<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> ('Marketing', 105, 'Dennis', 'Indolay'),<
</o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> ('Admin', 107, 'Sherry', 'Keller'),<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><
span style='font-size:10.0pt;font-family:"Lucida Console"'> ('Admin', 109, 'Carl', 'Nader'),<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> &
nbsp; ('Admin', 111, 'Ruth', 'Turret'),<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> ('Sales', 113, 'Andrew', 'Bonnet'),<o:p></o:p></span></p><p class=
MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> ('Sales', 115, 'Oscar', 'Hender'),<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-
family:"Lucida Console"'> ('Sales', 117, 'Justin', 'Braushere'),<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> ('Sales', 119, '', '
Smith');<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'><o:p> </o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"
Lucida Console"'>/* Concatenate the names of all employees, ordered by last name (LISTAGG used<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>** as a simple aggregate):<o:p><
/o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>*/<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>
SELECT LISTAGG(lastname)<o:p></o:p></span></p><p class=MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'> WITHIN GROUP (ORDER BY lastname) AS PERSONNEL<o:p></o:p></span></p><p class=
MsoNormal style='text-autospace:none'><span style='font-size:10.0pt;font-family:"Lucida Console"'>FROM employee;<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal>
<span style='color:#1F497D;mso-fareast-language:EN-US'>Will return: </span><span style='font-size:10.0pt;font-family:"Lucida Console"'>BonnetBraushereCrayHenderIndolayKellerLuangNaderSmithTurret</span><span style='color:#1F497D;mso-fareast-language:EN-US'
<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'>So my Licence fee angst is not perturbed by this at
least.<o:p></o:p></span></p><p class=MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='color:#1F497D;mso-fareast-language:EN-US'>Marty<o:p></o:p></span></p><p class=MsoNormal><
span style='color:#1F497D;mso-fareast-language:EN-US'><o:p> </o:p></span></p><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><p class=MsoNormal><b><span lang=EN-US>From:</span></b><span lang=EN-US> Adrian
Williamson <<a href="mailto:
adrian.williamson@rationalcommerce.com">
adrian.williamson@rationalcommerce.com</a>> <br><b>Sent:</b> 11 February 2021 15:32<br><b>To:</b> 'Ingres and related product discussion forum' <<a href="mailto:info-ingres@
lists.planetingres.org">
info-ingres@lists.planetingres.org</a>><br><b>Subject:</b> [Info-ingres] Listagg.....<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><div><p class=MsoNormal>String Aggregate Functions<o:p></o:p></
<p class=MsoNormal><b>Note: Valid for X100 tables only. – FECKERS.<o:p></o:p></b></p><p class=MsoNormal>String aggregate functions concatenate a set of string values.<o:p></o:p></p><p class=MsoNormal>LISTAGG<o:p></o:p></p><p class=
MsoNormal><o:p> </o:p></p><p class=MsoNormal>If I paid a licence fee I’d be upset…<o:p></o:p></p><p class=MsoNormal><o:p> </o:p></p></div></div></div></body></html>
--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)