Thanks Fernando. Its works fine for me in informix 9.
Post by Fernando NunesDatabase 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...