Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 910
  • Last Modified:

retrieving more than one item in group_by clause

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
jaggernat
Asked:
jaggernat
  • 13
  • 12
2 Solutions
 
sdstuberCommented:
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
 
jaggernatAuthor Commented:
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
 
sdstuberCommented:
ok, well hopefully it'll give a good start to the real db2 people.  :)  good luck
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
jaggernatAuthor Commented:
thx
0
 
jaggernatAuthor Commented:
anyone? can it be done in db2?

thx.
0
 
sdstuberCommented:
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
 
jaggernatAuthor Commented:
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
 
sdstuberCommented:
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
 
jaggernatAuthor Commented:
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
 
jaggernatAuthor Commented:
>> 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
 
sdstuberCommented:
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
 
jaggernatAuthor Commented:
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
 
jaggernatAuthor Commented:
>>ORDER BY A.USER_ID)  AS VARCHAR(100)),'<s>',''),'</s>',''),',')
i see how you are trying to replace the <s> with ' '


0
 
jaggernatAuthor Commented:
but for some reason its not working
0
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
jaggernatAuthor Commented:
sure, thanks for the help.
0
 
jaggernatAuthor Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
any of these working out for you?
0
 
cmaslenCommented:
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
 
sdstuberCommented:
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
 
jaggernatAuthor Commented:
sorry sdstuber, i just left you hanging in there.I appologize.
Thanks for all the solutions to everyone who contributed.
Good day!

0
 
sdstuberCommented:
I'm just glad something I posted helped
0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

  • 13
  • 12
Tackle projects and never again get stuck behind a technical roadblock.
Join Now