db2 select statement problem

ashrash · #1 (**permalink**) 08-29-09, 04:27

i have a table called admin_master
there are several field in this table

but i need to select two field admin_id and the location

there are many different location like bangalore,mumbai,pune ...etc

and there are number of admin_id with respect to location

this is the structure of my table
db2>admin_master

17912 PUNE
17918 BANGALORE
17919 BANGALORE
17920 MUMBAI
17921 MUMBAI
17922 BANGALORE
17923 BANGALORE
17924 BANGALORE
17925 BANGALORE
17926 BANGALORE
17927 BANGALORE
17928 BANGALORE
17929 BANGALORE
17930 BANGALORE
17931 BANGALORE
17932 BANGALORE
17933 BANGALORE
17934 BANGALORE
17935 BANGALORE
17936 BANGALORE
17937 BANGALORE
17938 BANGALORE
17940 BANGALORE
17941 BANGALORE
17943 BANGALORE

NOW HERE i want to select all auc_Ref_id location wise
like this

BANGALORE (17918,17919,17922,17923,17924,17925,17926,17927,1 7928,17929,17930,17931,17932,17933,17934,17935,179 36,17937,17938,17940,1794,17943)

PUNE()

MUMBAI(17912)

how do write the select statement in db2

so that i can get this result

pls very urgent ...............

przytula_guy · #2 (**permalink**) 08-29-09, 07:07

this has been discussed many times-see
Unpivot Query

Join IDUG in Rome this year
IDUG 2009 - Europe
and attend the session about certification preparation for DB2 LUW
DB2 9.5 for LUW Certification

ashrash · #3 (**permalink**) 08-29-09, 07:40

thanks for ur reply.and i saw your link to unpivot query ,but still i m conused

i want he result in this way:

BANGALORE

ADMIN_REF_ID (17918,17919,17922,17923,17924,17925,17926,17927,1 7928,17929,17930,17931,17932,17933,17934,17935,179 36,17937,17938,17940,1794,17943)

PUNE

ADMIN_REF_ID (17912)

MUMBAI
ADMIN_REF_ID (17920, 17920)

if it can be done by Unpivot Query please tell me how .as i m little confused and not clear

tonkuma · #4 (**permalink**) 08-29-09, 07:49

Quote:

this has been discussed many times-see
Unpivot Query

Yes, this has been discussed many times.
But, the link does not show the answer for the OP's question.

XMLAGG or recursive query would be the answer.

ashrash · #5 (**permalink**) 08-29-09, 08:02

this is the second time you are answering me .thank you .

what you have told i am trying now .if this is the only way to get the result

i will study and try to understand ....

DB2Plus · #6 (**permalink**) 08-29-09, 08:52

Quote:

Originally Posted by ashrash

thanks for ur reply.and i saw your link to unpivot query ,but still i m conused

i want he result in this way:

BANGALORE

ADMIN_REF_ID (17918,17919,17922,17923,17924,17925,17926,17927,1 7928,17929,17930,17931,17932,17933,17934,17935,179 36,17937,17938,17940,1794,17943)

PUNE

ADMIN_REF_ID (17912)

MUMBAI
ADMIN_REF_ID (17920, 17920)

if it can be done by Unpivot Query please tell me how .as i m little confused and not clear

You have to use recursive SQL for this.
Kara

tonkuma · #7 (**permalink**) 08-29-09, 11:05

Some examples(Tested on DB2 9.7 for Windows):

Code:

------------------------------ Commands Entered ------------------------------
WITH admin_master(admin_id, location) AS (
VALUES
 ('17912', 'PUNE')
,('17918', 'BANGALORE')
,('17919', 'BANGALORE')
,('17920', 'MUMBAI')
,('17921', 'MUMBAI')
,('17922', 'BANGALORE')
,('17923', 'BANGALORE')
,('17924', 'BANGALORE')
,('17925', 'BANGALORE')
,('17926', 'BANGALORE')
,('17927', 'BANGALORE')
,('17928', 'BANGALORE')
,('17929', 'BANGALORE')
,('17930', 'BANGALORE')
,('17931', 'BANGALORE')
,('17932', 'BANGALORE')
,('17933', 'BANGALORE')
,('17934', 'BANGALORE')
,('17935', 'BANGALORE')
,('17936', 'BANGALORE')
,('17937', 'BANGALORE')
,('17938', 'BANGALORE')
,('17940', 'BANGALORE')
,('17941', 'BANGALORE')
,('17943', 'BANGALORE')
)
SELECT location
     , SUBSTR(
       XMLCAST(
       XMLELEMENT(NAME x
          , XMLAGG(XMLTEXT(',' || admin_id)
                   ORDER BY admin_id) )
       AS VARCHAR(200) )
       , 2) AS admin_ref_id
  FROM admin_master
 GROUP BY location
;
------------------------------------------------------------------------------

LOCATION  ADMIN_REF_ID                                                                                                                                                                                            
--------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
BANGALORE 17918,17919,17922,17923,17924,17925,17926,17927,17928,17929,17930,17931,17932,17933,17934,17935,17936,17937,17938,17940,17941,17943                                                                     
MUMBAI    17920,17921                                                                                                                                                                                             
PUNE      17912                                                                                                                                                                                                   

  3 record(s) selected.

Code:

------------------------------ Commands Entered ------------------------------
WITH admin_master(admin_id, location) AS (
.....
)
,admin_master_seq AS (
SELECT admin_master.*
     , INTEGER(ROW_NUMBER()
               OVER(PARTITION BY location
                        ORDER BY admin_id)
              ) AS k
  FROM admin_master
)
,combine(k, location, admin_ref_id) AS (
SELECT 1
     , location
     , CAST(admin_id AS VARCHAR(200))
  FROM admin_master_seq
 WHERE k = 1
UNION ALL
SELECT pre.k + 1
     , pre.location
     , pre.admin_ref_id || ',' || new.admin_id
  FROM combine          pre
     , admin_master_seq new
 WHERE pre.k < 1000000000
   AND new.location = pre.location
   AND new.k = pre.k + 1
)
SELECT location
     , admin_ref_id
  FROM combine a
 WHERE k =
       (SELECT MAX(k)
          FROM combine b
         WHERE b.location = a.location
       )
;
------------------------------------------------------------------------------

LOCATION  ADMIN_REF_ID                                                                                                                                                                                            
--------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
PUNE      17912                                                                                                                                                                                                   
MUMBAI    17920,17921                                                                                                                                                                                             
BANGALORE 17918,17919,17922,17923,17924,17925,17926,17927,17928,17929,17930,17931,17932,17933,17934,17935,17936,17937,17938,17940,17941,17943                                                                     

  3 record(s) selected.

DB2Plus · #8 (**permalink**) 08-29-09, 11:20

This is really Great Job, Tonkuma !

Thanks, Kara S.

tonkuma · #9 (**permalink**) 08-31-09, 03:19

Here is a way of formatting SQL code by using new lines and indentions.

Code:

SELECT location
     , SUBSTR(
         XMLCAST(
           XMLELEMENT(
             NAME x
           , XMLAGG(
               XMLTEXT(',' || admin_id)
               ORDER BY admin_id
             )
           )
           AS VARCHAR(200)
         )
       , 2
       ) AS admin_ref_id
  FROM admin_master
 GROUP BY
       location
;

You can see easily the nested structure of functions/specifications and their parameters/operands
by drawing vertical lines. Like this:

Code:

SELECT location
     , SUBSTR(
       | XMLCAST(
       | | XMLELEMENT(
       | | | NAME x
       | | , XMLAGG(
       | | | | XMLTEXT(',' || admin_id)
       | | | | ORDER BY admin_id
       | | | )
       | | )
       | | AS VARCHAR(200)
       | )
       , 2
       ) AS admin_ref_id
  FROM admin_master
 GROUP BY
       location
;

Comma(",") is not a terminator.
It is used always paired with a following item in SQL language(and many of programming languages).
So, putting comma at the first position of a line might be not so strange way, if you would not stick to natural language custom.

ashrash · #10 (**permalink**) 09-01-09, 03:24

hi guys

sorry to all .....may be i was wrong to explain the exact problem

my problem

i have thousand of record in admin_master table ,what u people has shown to me is:= a static value

WITH admin_master(admin_id, location) AS (....
VALUES

...based on that you are trying select the admin_id location wise

basically what i want is i want to select the admin_id location wise and want to store admin_id in vector or hash table ,so that i can use this admin_id to retrieve the data from other table

means first select the data region wise then put all admin_id in vector and then retrieve all admin_id for other table

hope u have understood my problem ,sorry if i m asking stupid question or if i m wrong

tonkuma · #11 (**permalink**) 09-01-09, 04:17

Quote:

means first select the data region wise then put all admin_id in vector and then retrieve all admin_id for other table

Why you need to store all admin_id in somewhere?
Is it not enough to join retrieved admin_id with other table in the same query?

ashrash · #12 (**permalink**) 09-01-09, 04:56

Quote:

Originally Posted by tonkuma

Why you need to store all admin_id in somewhere?
Is it not enough to join retrieved admin_id with other table in the same query?

yes that also can be done

thats the easy way

but
in example u have shown is taking a fixed value then out of those you are trying to select the admin and location

if there are 1000 of records then how i can select statement.....

tonkuma · #13 (**permalink**) 09-01-09, 13:37

Quote:

in example u have shown is taking a fixed value then out of those you are trying to select the admin and location

if there are 1000 of records then how i can select statement.....

I couldn't understand what do you mean by "taking a fixed value".

Anyhow, how about something like this:

Code:

SELECT .....
  FROM admin_master  AS am
  JOIN <other table> AS ot
   ON  ot.admin_id = am.admin_id
   AND .....
 WHERE .....

tonkuma · #14 (**permalink**) 09-01-09, 13:51

Quote:

WITH admin_master(admin_id, location) AS (....
VALUES

...based on that you are trying select the admin_id location wise

By "WITH admin_master(.....)", I simulated create table and load sample data.
You can use the query with removing "WITH admin_master(.....)", then the query automatically use your admin_master table.

Internet Services

Web Development

Digital Marketing

Consumer Tech