Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
group_concat function in informix
2,634 views
Skip to first unread message
tom...@gmail.com
Aug 5, 2013, 2:39:22 PM8/5/13
to
hello,
a developer approached us today with this and wanted to know if it was possible to create something like this in Informix - anyone experience it before?
thanks
http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate/716655#716655
a developer approached us today with this and wanted to know if it was possible to create something like this in Informix - anyone experience it before?
thanks
http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate/716655#716655
Jonathan Leffler
Aug 5, 2013, 2:48:15 PM8/5/13
to Tom Lehr, Informix List - IIUG
On Mon, Aug 5, 2013 at 7:39 PM, <tom...@gmail.com> wrote:
A developer approached us today with this and wanted to know if it was possible to create something like this in Informix - anyone experience it before?
http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate/716655#716655
That question has an answer from me implementing GROUP_CONCAT for Informix. What more do you want?
-- Jonathan Leffler <jonathan...@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
Art Kagel
Aug 5, 2013, 4:31:46 PM8/5/13
to tom...@gmail.com, inform...@iiug.org
As Jonathan has noted, he has done this already and you provided a link to the code. Just copy it and install it.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Advanced DataTools (www.advancedatatools.com)
Blog: http://informix-myview.blogspot.com/
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
tom...@gmail.com
Aug 6, 2013, 9:16:06 AM8/6/13
to
just wondering if anyone had done it as a function/stored proc itself versus an aggregate based over a series of functions....Thanks
Fernando Nunes
Aug 6, 2013, 9:36:20 AM8/6/13
to Tom Lehr, IIUG Informix List
This kind of things usually can't be implemented as "simple" functions. The reason being that they change the way the engine solves a query. The discussion around ordering in stack overflow reflects that. The "real" group_concat() function in MySQL has several attributes like ORDER which solve that.
Besides that, the argument data typing itself can become a problem.
I've faced those kind of issues when I implemented ROW_NUMBER for a blog article post. I did it, but with a series of restrictions compared to the "real" ROW_NUMBER which was implemented in 12.1
In this scenario I'd like to test some other solutions, but for pure academic reasons. I became also curious with an answer that mentioned CONNECT BY as a solution, because we support it since 11.1 (maybe 11.50 but in any case in any supported version).
Also, if you implement Jonathan solution, but using SETs it may possibly solve (or allow us to solve) the ORDERING factor.
Regards
On Tue, Aug 6, 2013 at 2:16 PM, <tom...@gmail.com> wrote:
just wondering if anyone had done it as a function/stored proc itself versus an aggregate based over a series of functions....Thanks
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
tom...@gmail.com
Aug 9, 2013, 3:34:54 PM8/9/13
to
I was wondering about CONNECT BY - what is the solution using that?
Jonathan Leffler
Aug 9, 2013, 5:54:16 PM8/9/13
to Tom Lehr, Informix List - IIUG
On Fri, Aug 9, 2013 at 12:34 PM, <tom...@gmail.com> wrote:
I was wondering about CONNECT BY - what is the solution using that?
What makes you think CONNECT BY might be part of a solution? You wanted an aggregate function (the summary of a set of values within a group — the summary being a comma-separated list of the values within the group). CONNECT BY is for traversing hierarchical data structures; it does not, of itself, do any aggregation.
What is your real requirement?
(For anyone joining the conversation late and/or unthreaded, the question relates to GROUP_CONCAT and http://stackoverflow.com/questions/715350/.)
Fernando Nunes
Aug 9, 2013, 6:41:12 PM8/9/13
to Jonathan Leffler, Tom Lehr, Informix List - IIUG
Jonathan,
If I didn't confuse myself, CONNECT BY was mentioned in the stackoverflow discussion as a possible solution. But AFAIR no example was given.On Fri, Aug 9, 2013 at 10:54 PM, Jonathan Leffler <jonathan...@gmail.com> wrote:
On Fri, Aug 9, 2013 at 12:34 PM, <tom...@gmail.com> wrote:
I was wondering about CONNECT BY - what is the solution using that?
What makes you think CONNECT BY might be part of a solution? You wanted an aggregate function (the summary of a set of values within a group — the summary being a comma-separated list of the values within the group). CONNECT BY is for traversing hierarchical data structures; it does not, of itself, do any aggregation.
What is your real requirement?
(For anyone joining the conversation late and/or unthreaded, the question relates to GROUP_CONCAT and http://stackoverflow.com/questions/715350/.)
--Jonathan Leffler <jonathan...@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
Jonathan Leffler
Aug 9, 2013, 6:47:29 PM8/9/13
to Fernando Nunes, Tom Lehr, Informix List - IIUG
Hi Fernando,
Indeed, CONNECT BY was mentioned in another answer on Stack Overflow. However, there was no description or discussion or example of how it might be used, and I'm a tad sceptical that it is relevant. I'm willing to be shown that it can be used — in Oracle or Informix – but I'm far from convinced that it does the job that GROUP_CONCAT does.
On Fri, Aug 9, 2013 at 3:41 PM, Fernando Nunes <domus...@gmail.com> wrote:
RegardsI'm used to see very weird uses of CONNECT BY to do things far from obvious (specially in Oracle discussions).Jonathan,If I didn't confuse myself, CONNECT BY was mentioned in the stackoverflow discussion as a possible solution. But AFAIR no example was given.
On Fri, Aug 9, 2013 at 10:54 PM, Jonathan Leffler <jonathan...@gmail.com> wrote:
On Fri, Aug 9, 2013 at 12:34 PM, <tom...@gmail.com> wrote:
I was wondering about CONNECT BY - what is the solution using that?
What makes you think CONNECT BY might be part of a solution? You wanted an aggregate function (the summary of a set of values within a group — the summary being a comma-separated list of the values within the group). CONNECT BY is for traversing hierarchical data structures; it does not, of itself, do any aggregation.
What is your real requirement?
(For anyone joining the conversation late and/or unthreaded, the question relates to GROUP_CONCAT and http://stackoverflow.com/questions/715350/.)
--Jonathan Leffler <jonathan...@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Fernando Nunes
Aug 9, 2013, 7:00:13 PM8/9/13
to Jonathan Leffler, Tom Lehr, Informix List - IIUG
I will not argue. I don't know how to use it for that, nor if it's possible. And I'm with other interesting things to do now (you may be interested in my next blog article, as it is based around Google authenticator ;) ).
Meanwhile, I think we can go back to this on August 28, assuming the OP can wait until then or use your solution. The reason why I mention Augst 28 is because it's after this:https://events.na.collabserv.com/portal/wippages/register.php?id=2aba8f8a55&l=en-US
On Fri, Aug 9, 2013 at 11:47 PM, Jonathan Leffler <jonathan...@gmail.com> wrote:
Hi Fernando,Indeed, CONNECT BY was mentioned in another answer on Stack Overflow. However, there was no description or discussion or example of how it might be used, and I'm a tad sceptical that it is relevant. I'm willing to be shown that it can be used — in Oracle or Informix – but I'm far from convinced that it does the job that GROUP_CONCAT does.
On Fri, Aug 9, 2013 at 3:41 PM, Fernando Nunes <domus...@gmail.com> wrote:
RegardsI'm used to see very weird uses of CONNECT BY to do things far from obvious (specially in Oracle discussions).Jonathan,If I didn't confuse myself, CONNECT BY was mentioned in the stackoverflow discussion as a possible solution. But AFAIR no example was given.
On Fri, Aug 9, 2013 at 10:54 PM, Jonathan Leffler <jonathan...@gmail.com> wrote:
On Fri, Aug 9, 2013 at 12:34 PM, <tom...@gmail.com> wrote:
I was wondering about CONNECT BY - what is the solution using that?
What makes you think CONNECT BY might be part of a solution? You wanted an aggregate function (the summary of a set of values within a group — the summary being a comma-separated list of the values within the group). CONNECT BY is for traversing hierarchical data structures; it does not, of itself, do any aggregation.
What is your real requirement?
(For anyone joining the conversation late and/or unthreaded, the question relates to GROUP_CONCAT and http://stackoverflow.com/questions/715350/.)
--Jonathan Leffler <jonathan...@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--Jonathan Leffler <jonathan...@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
Fernando Nunes
Aug 9, 2013, 10:15:14 PM8/9/13
to Jonathan Leffler, Tom Lehr, Informix List - IIUG
Blog post done... Food for thought:
centau...@centaurus.onlinedomus.net:informix-> dbaccess -e stores group_concat.sql
Database selected.
DROP TABLE IF EXISTS mytable;
Table dropped.
CREATE TABLE mytable
(
col1 integer,
col2 char
);
Table created.
INSERT INTO mytable VALUES(1,'a');
1 row(s) inserted.
INSERT INTO mytable VALUES(1,'a');
1 row(s) inserted.
INSERT INTO mytable VALUES(1,'b');
1 row(s) inserted.
INSERT INTO mytable VALUES(1,'c');
1 row(s) inserted.
INSERT INTO mytable VALUES(1,'d');
1 row(s) inserted.
INSERT INTO mytable VALUES(2,'a');
1 row(s) inserted.
INSERT INTO mytable VALUES(2,'b');
1 row(s) inserted.
INSERT INTO mytable (col1) VALUES (3);
1 row(s) inserted.
DROP FUNCTION IF EXISTS group_concat;
Routine dropped.
CREATE FUNCTION group_concat (a COLLECTION) RETURNING LVARCHAR
DEFINE v VARCHAR;;
DEFINE ret LVARCHAR;;
LET RET="";;
IF a IS NULL THEN
RETURN NULL;;
ELSE
FOREACH
SELECT *
INTO v
FROM TABLE(a)
ORDER BY 1
IF ret = "" THEN
LET ret = TRIM(v);;
ELSE
LET ret = ret || ','||TRIM(v);;
END IF;;
END FOREACH;;
RETURN ret;;
END IF
END FUNCTION;
Routine created.
;
SELECT
col1, NVL(group_concat(MULTISET(SELECT UNIQUE col2 FROM mytable t2 WHERE t2.col1 = t1.col1)), 'NULL value')
FROM
mytable t1
GROUP BY col1,2;
col1 1
(expression) a,b,c,d
col1 2
(expression) a,b
col1 3
(expression) NULL value
3 row(s) retrieved.
Database closed.
centau...@centaurus.onlinedomus.net:informix->
Does it help?centau...@centaurus.onlinedomus.net:informix-> dbaccess -e stores group_concat.sql
Database selected.
DROP TABLE IF EXISTS mytable;
Table dropped.
CREATE TABLE mytable
(
col1 integer,
col2 char
);
Table created.
INSERT INTO mytable VALUES(1,'a');
1 row(s) inserted.
INSERT INTO mytable VALUES(1,'a');
1 row(s) inserted.
INSERT INTO mytable VALUES(1,'b');
1 row(s) inserted.
INSERT INTO mytable VALUES(1,'c');
1 row(s) inserted.
INSERT INTO mytable VALUES(1,'d');
1 row(s) inserted.
INSERT INTO mytable VALUES(2,'a');
1 row(s) inserted.
INSERT INTO mytable VALUES(2,'b');
1 row(s) inserted.
INSERT INTO mytable (col1) VALUES (3);
1 row(s) inserted.
DROP FUNCTION IF EXISTS group_concat;
Routine dropped.
CREATE FUNCTION group_concat (a COLLECTION) RETURNING LVARCHAR
DEFINE v VARCHAR;;
DEFINE ret LVARCHAR;;
LET RET="";;
IF a IS NULL THEN
RETURN NULL;;
ELSE
FOREACH
SELECT *
INTO v
FROM TABLE(a)
ORDER BY 1
IF ret = "" THEN
LET ret = TRIM(v);;
ELSE
LET ret = ret || ','||TRIM(v);;
END IF;;
END FOREACH;;
RETURN ret;;
END IF
END FUNCTION;
Routine created.
;
SELECT
col1, NVL(group_concat(MULTISET(SELECT UNIQUE col2 FROM mytable t2 WHERE t2.col1 = t1.col1)), 'NULL value')
FROM
mytable t1
GROUP BY col1,2;
col1 1
(expression) a,b,c,d
col1 2
(expression) a,b
col1 3
(expression) NULL value
3 row(s) retrieved.
Database closed.
centau...@centaurus.onlinedomus.net:informix->
On Sat, Aug 10, 2013 at 12:00 AM, Fernando Nunes <domus...@gmail.com> wrote:
RegardsBut again... I have some other things waiting before that.Besides all this, I would like to investigate SETs as I think it may help with some of the problems around your solution.Personally I've used CONNECT BY "for test data generation". But to be honest I copied it from forums of other database users. Besides that I did pass my eyes over uses of CONNECT BY that made me thinking I know nothing about it!Hopefully Jerry may help us.I will not argue. I don't know how to use it for that, nor if it's possible. And I'm with other interesting things to do now (you may be interested in my next blog article, as it is based around Google authenticator ;) ).Meanwhile, I think we can go back to this on August 28, assuming the OP can wait until then or use your solution. The reason why I mention Augst 28 is because it's after this:
https://events.na.collabserv.com/portal/wippages/register.php?id=2aba8f8a55&l=en-US
On Fri, Aug 9, 2013 at 11:47 PM, Jonathan Leffler <jonathan...@gmail.com> wrote:
Hi Fernando,Indeed, CONNECT BY was mentioned in another answer on Stack Overflow. However, there was no description or discussion or example of how it might be used, and I'm a tad sceptical that it is relevant. I'm willing to be shown that it can be used — in Oracle or Informix – but I'm far from convinced that it does the job that GROUP_CONCAT does.
On Fri, Aug 9, 2013 at 3:41 PM, Fernando Nunes <domus...@gmail.com> wrote:
RegardsI'm used to see very weird uses of CONNECT BY to do things far from obvious (specially in Oracle discussions).Jonathan,If I didn't confuse myself, CONNECT BY was mentioned in the stackoverflow discussion as a possible solution. But AFAIR no example was given.
On Fri, Aug 9, 2013 at 10:54 PM, Jonathan Leffler <jonathan...@gmail.com> wrote:
On Fri, Aug 9, 2013 at 12:34 PM, <tom...@gmail.com> wrote:
I was wondering about CONNECT BY - what is the solution using that?
What makes you think CONNECT BY might be part of a solution? You wanted an aggregate function (the summary of a set of values within a group — the summary being a comma-separated list of the values within the group). CONNECT BY is for traversing hierarchical data structures; it does not, of itself, do any aggregation.
What is your real requirement?
(For anyone joining the conversation late and/or unthreaded, the question relates to GROUP_CONCAT and http://stackoverflow.com/questions/715350/.)
--Jonathan Leffler <jonathan...@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
--Jonathan Leffler <jonathan...@gmail.com> #include <disclaimer.h>
Guardian of DBD::Informix - v2013.0521 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be amused."
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
carvajal...@gmail.com
Nov 29, 2016, 11:56:18 AM11/29/16
to
Thanks Fernando. Its works fine for me in informix 9.
0 new messages