Results 1 to 14 of 14
  1. #1
    Join Date
    Aug 2009
    Posts
    19

    Unhappy Unanswered: db2 select statement problem

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

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    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
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Aug 2009
    Posts
    19

    thanks for the reply

    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

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  5. #5
    Join Date
    Aug 2009
    Posts
    19

    thanks tonkuma

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

  6. #6
    Join Date
    Jul 2009
    Posts
    150
    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
    Last edited by DB2Plus; 08-29-09 at 09:55.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  8. #8
    Join Date
    Jul 2009
    Posts
    150
    This is really Great Job, Tonkuma !

    Thanks, Kara S.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  10. #10
    Join Date
    Aug 2009
    Posts
    19

    problem not solved

    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

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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?

  12. #12
    Join Date
    Aug 2009
    Posts
    19

    hi tonkuma

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

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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 .....

  14. #14
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •