DB/2 LUW – String Aggregation

Background

The whole point of our last post is to lay the foundation for this one.

Last Post

The last post is :-

DB/2 – LUW – DDL & DML – Schema, Table & Adding Data
Link

And, it deals with creating schema ( government ), table ( government.city) and populating the table.

Per State, Aggregate City

In this post, we will aggregate cities for each State.

Choices

  1. LISTAGG
  2. XMLGROUP
  3. User Defined Function

Code

LISTAGG

Outline

  1. ListAgg
    • For each group derived from the group by clause :-
      • Detailed data is processed
      • If the “Within Group” option is included we are able to dictate the Ordering of the detail elements

Usage


select
          tblC."state"

        , LISTAGG
                (
                      tblC."city"
                    , ', '
                )
              WITHIN GROUP
              (
                ORDER BY
                    tblC."city" ASC
              )

              AS "listofCities"

        , count(*)
            AS "numberofCities"

from "government"."city" tblC

group by
        tblC."state"

order by
        tblC."state"

XMLGroup

Outline

  1. XMLGroup
    • For each grouping deducted from the group by clause :-
      • An XML Node is created
        • In our case a separator (,) and the column ( city )
  2. XMLCast
    • XMLCast is used to convert the XML Node to a string
  3. The substring function strings out the leading delimiter (,)

Usage

select
          tblC."state"
            AS "state"

        , substring
            (
                XMLCAST
                (
                    XMLGroup
                    (
                        CONCAT
                         (
                            ', '
                            , tblC."city"
                        )
                        AS xCity
                    )
                    AS varchar(4000)
                )
                , 2

            ) AS "listofCities"

        , count(*)
            AS "numberofCities"

from "government"."city" tblC

group by
        tblC."state"

order by
        tblC."state"

Function

Outline

  1. Declare Row Type
  2. Declare Cursor
    • Fetch City
    • Filter on State = <passed in State>
  3. At Completion of Cursor, set EOF Flag to 1
  4. Open Cursor
  5. Fetch Cursor into Row Type Variable
  6. Close Cursor

government.StateCitiesAsString

CREATE OR REPLACE FUNCTION government.StateCitiesAsString
(
      vState VARCHAR(60)
    , vSep   VARCHAR(2) DEFAULT ', '
)
RETURNS VARCHAR(4000)
NO EXTERNAL ACTION
BEGIN

    DECLARE TYPE recordCity AS
    ROW
    (
        city VARCHAR(60)
    )
    ;   

    DECLARE rowCity  recordCity;
    DECLARE v_result   varchar(4000);
    DECLARE EOF      INT;

    DECLARE v_lenofResult int;
    DECLARE v_lenofSep int;

    DECLARE cursorCity CURSOR FOR

    SELECT  "city"

    FROM    "government"."city" tblC

    where   tblC."state" = vState

    ORDER   BY
            tblC."city"

    ;

    DECLARE CONTINUE HANDLER FOR NOT FOUND SET EOF = 1;

    SET v_result = '';
    SET EOF = 0;

    OPEN cursorCity;

    FETCH FROM cursorCity
    INTO rowCity
    ;

    WHILE EOF = 0 DO

        if (v_result != '') THEN

            set v_result = concat(v_result, vSep);

        end if;

        set v_result = concat(v_result, rowCity.city);

        FETCH FROM cursorCity
        INTO rowCity
        ;

    END WHILE;

    CLOSE cursorCity;

    RETURN (v_result);

END
//

Lab

SELECT
          tblC."state"

        , government.StateCitiesAsString
            (
                  tblC."state"
                , ', '
            )
            as "listofCities"

FROM   "government"."city" tblC

GROUP BY
          "state"

ORDER BY
          "state"

Output

query_output_20180911_0520PM

Listening

Here is to to those guys who got me in the game.
And, kept the lights on through all these years.

Rick Ross “Idols Become Rivals”
Link ( Video )
Link ( Lyrics )

Said what they had to when they had to.

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s