Discussion:
group_concat function in informix
(too old to reply)
t***@gmail.com
2013-08-05 18:39:22 UTC
Permalink
Raw Message
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
Jonathan Leffler
2013-08-05 18:48:15 UTC
Permalink
Raw Message
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 <***@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."
t***@gmail.com
2013-08-06 13:16:06 UTC
Permalink
Raw Message
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
2013-08-06 13:36:20 UTC
Permalink
Raw Message
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
Post by t***@gmail.com
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
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...
t***@gmail.com
2013-08-09 19:34:54 UTC
Permalink
Raw Message
I was wondering about CONNECT BY - what is the solution using that?
Jonathan Leffler
2013-08-09 21:54:16 UTC
Permalink
Raw Message
Post by t***@gmail.com
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/<http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate/716655#716655>
.)
--
Jonathan Leffler <***@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
2013-08-09 22:41:12 UTC
Permalink
Raw Message
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.
I'm used to see very weird uses of CONNECT BY to do things far from obvious
(specially in Oracle discussions).
Regards


On Fri, Aug 9, 2013 at 10:54 PM, Jonathan Leffler <
Post by t***@gmail.com
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/<http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate/716655#716655>
.)
--
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
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
2013-08-09 22:47:29 UTC
Permalink
Raw Message
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.
Post by Fernando Nunes
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.
I'm used to see very weird uses of CONNECT BY to do things far from
obvious (specially in Oracle discussions).
Regards
On Fri, Aug 9, 2013 at 10:54 PM, Jonathan Leffler <
Post by t***@gmail.com
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/<http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate/716655#716655>
.)
--
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
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 <***@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
2013-08-09 23:00:13 UTC
Permalink
Raw Message
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

Hopefully Jerry may help us.
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!


Besides all this, I would like to investigate SETs as I think it may help
with some of the problems around your solution.
But again... I have some other things waiting before that.
Regards


On Fri, Aug 9, 2013 at 11:47 PM, Jonathan Leffler <
Post by Jonathan Leffler
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.
Post by Fernando Nunes
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.
I'm used to see very weird uses of CONNECT BY to do things far from
obvious (specially in Oracle discussions).
Regards
On Fri, Aug 9, 2013 at 10:54 PM, Jonathan Leffler <
Post by t***@gmail.com
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/<http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate/716655#716655>
.)
--
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
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...
--
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...
Fernando Nunes
2013-08-10 02:15:14 UTC
Permalink
Raw Message
Blog post done... Food for thought:

***@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.

***@centaurus.onlinedomus.net:informix->


Does it help?
Post by Fernando Nunes
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
https://events.na.collabserv.com/portal/wippages/register.php?id=2aba8f8a55&l=en-US
Hopefully Jerry may help us.
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!
Besides all this, I would like to investigate SETs as I think it may help
with some of the problems around your solution.
But again... I have some other things waiting before that.
Regards
On Fri, Aug 9, 2013 at 11:47 PM, Jonathan Leffler <
Post by Jonathan Leffler
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.
Post by Fernando Nunes
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.
I'm used to see very weird uses of CONNECT BY to do things far from
obvious (specially in Oracle discussions).
Regards
On Fri, Aug 9, 2013 at 10:54 PM, Jonathan Leffler <
Post by t***@gmail.com
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/<http://stackoverflow.com/questions/715350/show-a-one-to-many-relationship-as-2-columns-1-unique-row-id-comma-separate/716655#716655>
.)
--
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
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...
--
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...
--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
c***@gmail.com
2016-11-29 16:56:15 UTC
Permalink
Raw Message
Thanks Fernando. Its works fine for me in informix 9.
Post by Fernando Nunes
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.
Does it help?
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 ;) ).
https://events.na.collabserv.com/portal/wippages/register.php?id=2aba8f8a55&l=en-US
Hopefully Jerry may help us.
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!
Besides all this, I would like to investigate SETs as I think it may help with some of the problems around your solution.
But again... I have some other things waiting before that.
Regards
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.
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.
I'm used to see very weird uses of CONNECT BY to do things far from obvious (specially in Oracle discussions).
Regards
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/.)
--
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
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...
--
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...
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Art Kagel
2013-08-05 20:31:46 UTC
Permalink
Raw Message
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.
Post by t***@gmail.com
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
_______________________________________________
Informix-list mailing list
http://www.iiug.org/mailman/listinfo/informix-list
Loading...