• group_concat function in informix

    From carvajal.gonzalo@gmail.com@21:1/5 to All on Tue Nov 29 08:56:15 2016
    Thanks Fernando. Its works fine for me in informix 9.


    El viernes, 9 de agosto de 2013, 23:15:14 (UTC-3), Fernando Nunes escribió:
    Blog post done... Food for thought:

    centaurus_tcp@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_tcp@centaurus.onlinedomus.net:informix->



    Does it help?




    On Sat, Aug 10, 2013 at 12:00 AM, Fernando Nunes <domus...@gmail.com> wrote:








    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 <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:








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




    --
    Fernando Nunes
    Portugal

    http://informix-technology.blogspot.com
    My email works... but I don't check it frequently...

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)