Solved

retrieving more than one item in group_by clause

Posted on 2009-04-08
27
835 Views
Last Modified: 2012-05-06
hi friends
i had asked a similar question yesterday

i have a query in db2 like this

select A.FIRST_NAME, A.LAST_NAME, count(A.USER_ID) from schema.USERS A GROUP BY A.FIRST_NAME, A.LAST_NAME HAVING  count(A.USER_ID) > 1

A person can have more than one USER_ID. example data

firstname     lastname       userid

raj                  becker            100
raj                  becker            200
sam                 raian             300


Is there anyway i can modify the above query to get the user_id aswell.
My result needs to be something like

A.FIRST_NAME    A.LAST_NAME   count(A.USER_ID)        USER_ID
raj                               becker                          2                          100,200


any ideas greatly appreciated

thanx
J
0
Comment
Question by:jaggernat
  • 13
  • 12
27 Comments
 
LVL 73

Expert Comment

by:sdstuber
ID: 24098501
I'm not sure but a variation on the Oracle answer I posted in your other question might work here too

instead of using the oracle xmltype  getstringval  method, use the extractvarchar function
I'm not really a db2 guy, but thought I'd at least try to help


SELECT firstname,
         lastname,
         COUNT(userid),        
         RTRIM(
             extractvarchar(
                 XMLAGG(XMLELEMENT("s", userid || ',') ORDER BY userid),
                 '/s/text()'
             ),
             ','
         )
    FROM your_table
GROUP BY firstname, lastname
  HAVING COUNT(userid) > 1
ORDER BY firstname, lastname;
0
 
LVL 10

Author Comment

by:jaggernat
ID: 24098558
hmm, i ran the query

SELECT A.FIRST_NAME, A.LAST_NAME, count(A.USER_ID),        
         RTRIM(
             extractvarchar(
                 XMLAGG(XMLELEMENT("s", A.USER_ID || ',') ORDER BY A.USER_ID),
                 '/s/text()'
             ),
             ','
         )
    FROM PROD.USERS A
GROUP BY A.FIRST_NAME, A.LAST_NAME
  HAVING COUNT(A.USER_ID) > 1
ORDER BY A.FIRST_NAME, A.LAST_NAME;

got this error::

ERROR [42703] [IBM][DB2] SQL0206N  "s" is not valid in the context where it is used.  SQLSTATE=42703
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24098660
ok, well hopefully it'll give a good start to the real db2 people.  :)  good luck
0
 
LVL 10

Author Comment

by:jaggernat
ID: 24098753
thx
0
 
LVL 10

Author Comment

by:jaggernat
ID: 24099093
anyone? can it be done in db2?

thx.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24099141
SELECT A.FIRST_NAME, A.LAST_NAME, count(A.USER_ID),        
         RTRIM(
             extractvarchar(
                 XMLAGG(XMLELEMENT(NAME "s", A.USER_ID || ',') ORDER BY A.USER_ID),
                 '/s'
             ),
             ','
         )
    FROM PROD.USERS A
GROUP BY A.FIRST_NAME, A.LAST_NAME
  HAVING COUNT(A.USER_ID) > 1
ORDER BY A.FIRST_NAME, A.LAST_NAME;

0
 
LVL 10

Author Comment

by:jaggernat
ID: 24099192
thx sdstuber..
i am getting this error now::
ERROR [42884] [IBM][DB2] SQL0440N  No authorized routine named "EXTRACTVARCHAR" of type "" having compatible arguments was found.  SQLSTATE=42884
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24099217
what does this yield?  if you don't have an xml viewer, you may have to wrap it in xml2clob.
maybe that will be sufficient.


SELECT A.FIRST_NAME, A.LAST_NAME, count(A.USER_ID),                  
                 XMLAGG(XMLELEMENT(NAME "s", A.USER_ID || ',') ORDER BY A.USER_ID),
                 '/s'
                 FROM PROD.USERS A
GROUP BY A.FIRST_NAME, A.LAST_NAME
  HAVING COUNT(A.USER_ID) > 1
ORDER BY A.FIRST_NAME, A.LAST_NAME;
0
 
LVL 10

Author Comment

by:jaggernat
ID: 24099297
hey i think we are getting close,, no errors this time but the result set comes out like this

FIRST_NAME     LAST_NAME                        1                                                                      2*
Alice                     roy                         2            <s>roy1 ,</s><s>roy2,</s>                                    /s

roy1 and  roy2 are user ids.

thx
0
 
LVL 10

Author Comment

by:jaggernat
ID: 24099326
>> if you don't have an xml viewer, you may have to wrap it in xml2clob
i am actually running the sql in my TOAD
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24099569
I wanted make sure the aggregation itself was working.

now you need to find the function that will extract the text of the generated xml string.

try this...
SELECT A.FIRST_NAME, A.LAST_NAME, count(A.USER_ID) cnt,

                 rtrim(replace(replace(XMLSERIALIZE(CONTENT XMLAGG(XMLELEMENT(NAME "s", A.USER_ID ||

        ',')

        ORDER BY A.USER_ID)  AS VARCHAR(100)),'<s>',''),'</s>',''),',')

                 ids

                 FROM PROD.USERS A

GROUP BY A.FIRST_NAME, A.LAST_NAME

  HAVING COUNT(A.USER_ID) > 1

ORDER BY A.FIRST_NAME, A.LAST_NAME;

Open in new window

0
 
LVL 10

Author Comment

by:jaggernat
ID: 24099668
it shows an error (red line) under XMLAGG
it says 'Incorrect syntax near XMLAGG'

and when i run it it throws this error::
ERROR [42601] [IBM][DB2] SQL0104N  An unexpected token "VARCHAR" was found following "".  Expected tokens may include:  "CLOB CHAR CHARACTER BLOB BINARY DBCLOB".  SQLSTATE=42601
0
 
LVL 10

Author Comment

by:jaggernat
ID: 24099799
>>ORDER BY A.USER_ID)  AS VARCHAR(100)),'<s>',''),'</s>',''),',')
i see how you are trying to replace the <s> with ' '


0
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 10

Author Comment

by:jaggernat
ID: 24099806
but for some reason its not working
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24099819
I've reformatted to try to make it easier to find the error but I don't know

Near as I can tell I'm using XMLSERIALIZE correctly.

Sorry, I think really is as far as I can take this one now.
SELECT A.FIRST_NAME, A.LAST_NAME, 

       COUNT(A.USER_ID) as cnt,

       RTRIM(

         REPLACE(

           REPLACE(

               XMLSERIALIZE(

                  CONTENT 

                      XMLAGG(XMLELEMENT(NAME "s", A.USER_ID ||',') ORDER BY A.USER_ID)  

                  AS VARCHAR(100)

               ),

               '<s>',''

           ),

           '</s>',''

         ),

       ',') as ids

FROM PROD.USERS A

GROUP BY A.FIRST_NAME, A.LAST_NAME

HAVING COUNT(A.USER_ID) > 1

ORDER BY A.FIRST_NAME, A.LAST_NAME;

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24099838
you need some way to get the XML data returned from XMLAGG to become a string so you can do the replace and rtrim's.

but the only way I know to do that is with XMLSERIALIZE (or the deprecated xml2clob, but you don't really need a clob, you could try it though I guess)

if there are other functions for either extracting the text of the xml <s> string nodes themselves or  turning the xml into a string I don't know.
0
 
LVL 10

Author Comment

by:jaggernat
ID: 24099849
sure, thanks for the help.
0
 
LVL 10

Author Comment

by:jaggernat
ID: 24099990
i was just trying to understnad what this line means

>>XMLAGG(XMLELEMENT(NAME "UserId", A.USER_ID || ',') ORDER BY A.USER_ID)

XMLAGG is the aggregate function i guess ,right which aggregates the user ids.
what does NAME mean?
any idea

thanks
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24100081
each xmlelement must have a tag,  NAME gives the tag it's name


<s>roy1 ,</s><s>roy2,</s>  


NAME "s"  is what made the tags be "s"

 "UserId" is fine too, just change the replace commands to replace "UserId" with Null
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24100598
since the DB2 zone is relatively low traffic compared to some of the other database zones you might want to add a zone like Database Miscellaneous or maybe SQL Query Syntax and see if you can get some cross platform help, or at least more exposure.

Or ask the admins to send a note to add zones and send alerts to others better qualified than me
0
 
LVL 73

Accepted Solution

by:
sdstuber earned 450 total points
ID: 24108918
Here's another way, I got it from a coworker so not really something I can debug much for you.

This isn't legal syntax for Oracle, but for DB2 it should be.
The corresponding Oracle way of doing this would be MUCH less efficient than xml aggregation, I imagine the DB2 engine will likely be similar, but maybe not.

good luck!
WITH x (KEY1,KEY2, val, rnum)

        AS (SELECT a.first_name,

                   a.last_name,

                   varchar(RTRIM(a.user_id), 1000),

                   ROW_NUMBER() OVER (PARTITION BY a.first_name, a.last_name)

              FROM prod.users a),

    y (KEY1,KEY2, str, cnt, cnt_max)

     AS (  SELECT key1, key2, varchar('', 1000), 0, MAX(rnum)

             FROM x

         GROUP BY key1, key2

         UNION ALL

         SELECT y.key1, y.key2, y.str || ',' || RTRIM(char(x.val)), y.cnt + 1, y.cnt_max

           FROM x, y

          WHERE x.key1 = y.key1 AND x.key2 = y.key2 AND x.rnum = y.cnt + 1 AND y.cnt < y.cnt_max)

SELECT y.key1, y.key2, SUBSTR(str, 2, 999)

  FROM y

 WHERE y.cnt = y.cnt_max;

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24197719
any of these working out for you?
0
 
LVL 1

Assisted Solution

by:cmaslen
cmaslen earned 50 total points
ID: 24303798
Hi J,

I'm assuming you're using a version of DB2 with the XMLAGG aggregate function. I think it was introduced in version 9. XMLAGG is an aggregate function that reduces grouped items into XML. More info can be found at:

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/doc/r0022183.htm

The XML elements can then be serialised to a varchar. From here the vachar of XML has the start tag replaced with blank and the end tag replaced with ', '. The whole thing is wrapped in an outter select so you can get rid of the trailing comma.

The alternative approach is to use recursion, but this is (a little) easier to read and performs significantly better.

The code snippet uses a values constructor to generate your sample data - saves me using DDL to create a test table. Substitue my t table for your table.

Christian.


select tt.first_name, 

      tt.last_name, 

      SUBSTR( tt.user_list, 1, LENGTH( tt.user_list ) - 2 ) AS user_list

from   (

      select t.first_name, 

             t.last_name, REPLACE ( REPLACE

                      ( XMLSERIALIZE

                        ( CONTENT XMLAGG ( 

                          XMLELEMENT ( NAME A, user_Id ) 

                                      ORDER BY user_Id)

                                          AS VARCHAR (500) ), '<A>', '' ),

                                          '</A>', ', ' ) AS User_List

      from   ( values ( 'raj', 'becker', 100),

              ('raj', 'becker', 200),

              ('sam', 'raian', 300)

                            ) as t(first_name, last_name, user_Id)

      group by first_name, last_name

      ) tt;
 

      

Open in new window

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24492762
I think I outlined two good methods to try and cmaslen has offered another variation on the xmlagg method.

I am not a DB2 person though, so I can't confirm if any of the above "really" work though, but hopefully they at least provide some useful direction.

However, if they aren't helpful at all and this question needs to be deleted, I can live with that too.
0
 
LVL 10

Author Comment

by:jaggernat
ID: 24493901
sorry sdstuber, i just left you hanging in there.I appologize.
Thanks for all the solutions to everyone who contributed.
Good day!

0
 
LVL 73

Expert Comment

by:sdstuber
ID: 24494083
I'm just glad something I posted helped
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now