Results 1 to 7 of 7
  1. #1
    Join Date
    Aug 2013
    Posts
    14

    Unanswered: Recursive SQL Duplicates

    I'm having issues with my script bringing back duplicate names and I'm not sure how to fix it. My script concatenates columns into a row. Basically I have admin names that I need listed in the same row and column of the building they are assigned. The admin names are listed multiple times and when I concatenate the rows it lists the admin name as follows (John Doe | John Doe). Point of Contact and list sometimes contain different names. I need to exclude the duplicate names in the list. I have attached a screen shot of the current and desired results. Your help is appreciated.

    Code:
       WITH
        /*****************************************************
        *** The cte was used to generate test data easily. ***
        *****************************************************/
        sample_data
    ( rownum, project_id , project_name ,  name_last , name_first, point_of_contact, building_id, building_name, hours_used,
    hours_to_use, percentage_used, capability, bucket_a, bucket_b, bucket_c  ) AS
    (
    VALUES
      (1, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 2, 'Main' , 80, 0, 1.0, 6000, 12000, 9600, 5100)
    , (2, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 2 , 'Main', 80,  0, 1.0, 7000, 12500, 8000, 4000)
    , (3, 10 , 'ELITE', 'Jones', 'Amber', 'Susan Gamb | Amber Jones', 2, 'Main', 80, 0, 1.0, 6000, 12000, 9600, 5100)
    , (4, 10 , 'ELITE', 'Jones', 'Amber', 'Susan Gamb | Amber Jones', 2, 'Main', 80, 0, 1.0, 7000, 12500, 8000, 4000)
    , (1, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 6 ,'Warehouse', 40, 40, .5, 6000, 12500, 9600, 5100)
    , (2, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 7000, 12000, 8000, 4000)
    , (3, 10 , 'ELITE', 'Jones', 'Amber',  'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 6000, 12500, 9600, 5100)
    , (4, 10 , 'ELITE', 'Jones', 'Amber',  'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 6000, 12000, 8000, 4000 )
    , (1, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown', 2 , 'Main', 60,  20, .75, 5000, 1000, 1200, 4100 )
    , (2, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown', 2, 'Main',  60, 20, .75, 4000, 1500, 1000, 3000)
    , (3, 1040 , 'ROADRUNNER', 'Brown', 'Rob', 'Paul Sugar | Rob Brown', 2, 'Main', 60, 20, .75, 4500, 2000, 1200, 4100)
    , (4, 1040 , 'ROADRUNNER', 'Brown', 'Rob', 'Paul Sugar | Rob Brown', 2, 'Main', 60, 20, .75, 4000, 1500, 1000, 3000)
    , (1, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown',  6 , 'Warehouse', 60, 20, .75, 4500, 2000, 1200, 4100)
    , (2, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4000, 1500, 1000, 3000)
    , (2, 1040 , 'ROADRUNNER',  'Brown', 'Rob', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4500, 2000, 1200, 4100 )
    , (3, 1040 , 'ROADRUNNER',  'Brown', 'Rob', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4000, 1500, 1000, 3000)
    
    )
        ,
    
               t2(PROJECT_ID, LIST, POINT_OF_CONTACT, PROJECT_NAME, BUILDING_ID, BUILDING_NAME, HOURS_USED, HOURS_TO_USE, PERCENTAGE_USED, CAPABILITY,
               BUCKET_A, BUCKET_B, BUCKET_C, cnt) AS
            ( SELECT    PROJECT_ID,
                        VARCHAR(NAME_FIRST CONCAT ' ' CONCAT NAME_LAST, 6000),
                        POINT_OF_CONTACT,
                        PROJECT_NAME,
                        BUILDING_ID,
                        BUILDING_NAME,
                        HOURS_USED,
                        HOURS_TO_USE,
                        PERCENTAGE_USED,
                        CAPABILITY,
                        BUCKET_A,
                        BUCKET_B,
                        BUCKET_C,
                        1
            FROM SAMPLE_DATA
                    WHERE rowNum = 1
                    UNION ALL
            SELECT
                t2.PROJECT_ID,
                        t2.list || ' | ' || SAMPLE_DATA.NAME_FIRST CONCAT ' ' CONCAT SAMPLE_DATA.NAME_LAST,
                        SAMPLE_DATA.POINT_OF_CONTACT,
                        SAMPLE_DATA.PROJECT_NAME,
                        SAMPLE_DATA.BUILDING_ID,
                        SAMPLE_DATA.BUILDING_NAME,
                        SAMPLE_DATA.HOURS_USED,
                        SAMPLE_DATA.HOURS_TO_USE,
                        SAMPLE_DATA.PERCENTAGE_USED,
                        SAMPLE_DATA.CAPABILITY,
                        SAMPLE_DATA.BUCKET_A,
                        SAMPLE_DATA.BUCKET_B,
                        SAMPLE_DATA.BUCKET_C,
                        t2.cnt + 1
            FROM t2, SAMPLE_DATA
    
                    WHERE t2.PROJECT_ID = SAMPLE_DATA.PROJECT_ID
                    AND          t2.BUILDING_ID = SAMPLE_DATA.BUILDING_ID
                    AND   t2.cnt + 1 = SAMPLE_DATA.rowNum
                     )    
            SELECT
                       PROJECT_ID,
                       PROJECT_NAME,
                       POINT_OF_CONTACT,
                       BUILDING_ID,
                       BUILDING_NAME,
                       HOURS_USED,
                       HOURS_TO_USE,
                       PERCENTAGE_USED,
                       CAPABILITY,
                       BUCKET_A,
                       BUCKET_B,
                       BUCKET_C,
                       list
            FROM t2
                    WHERE ( PROJECT_ID, BUILDING_ID, cnt ) IN (
            SELECT PROJECT_ID, BUILDING_ID, MAX(rowNum)
            FROM SAMPLE_DATA
                    GROUP BY PROJECT_ID, BUILDING_ID )    
                    order by PROJECT_NAME
    Attached Thumbnails Attached Thumbnails Capture.PNG   Capture1.PNG  
    Last edited by wtolbert; 09-30-13 at 10:49.

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    It's showing up twice due to the admins, being admins for multiple buildings. Maybe if you just join your cte on the project id, it will work. In other words:
    Code:
     FROM t2, t1 
                WHERE t2.project_id = t1.project_id 
                AND	  t2.BUILDING_id = t1.BUILDING_id
                AND   t2.cnt + 1 = t1.rowNum )
    should be:
    Code:
     FROM t2, t1 
                WHERE t2.project_id = t1.project_id 
                AND   t2.cnt + 1 = t1.rowNum )

  3. #3
    Join Date
    Aug 2013
    Posts
    14
    Thank you for the suggestion. Unfortunately that did not work.

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without having your schema, data, DB2 version and fixpack I can offer some insight but not a true analysis or solution.

    Using RowNumber() in your SELECT list makes using DISTINCT pointless because every row will be distinct. If you take all of the columns from the CTE base SELECT list except for RowNumber and push them into a derived table (in the FROM clause), then I think that you'll get what you expected.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Pat Phelan View Post
    Without having your schema, data, DB2 version and fixpack I can offer some insight but not a true analysis or solution.

    ...
    "DB2 version and fixpack" are imporant.
    Because, some new functionalities may simplify your query(e.g. remove neccesity of recursive query in your case).

    "your schema, data" are usefull to reduce extra/useless dialogue between you and us.
    Because, we can test our idea(sample query) before posting it to debug simple syntax error or incorrect output, so on...

  6. #6
    Join Date
    Aug 2013
    Posts
    14
    I'm using version 10.1 (V10.1) not sure how to identify the fix pack. I was able to find a resolution but maybe there is a simpler way? I have included my script below.

    Code:
    WITH
    /*****************************************************
    *** Sample Data  ***
    *****************************************************/
     sample_data
    ( rownum, project_id , project_name , point_of_contact, name_last , name_first, building_id, building_name, hours_used, 
    hours_to_use, percentage_used, capability, bucket_a, bucket_b, bucket_c  ) AS 
    (
    VALUES
      (1, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 2, 'Main' , 80, 0, 1.0, 6000, 12000, 9600, 5100)
    , (2, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 2 , 'Main', 80,  0, 1.0, 7000, 12500, 8000, 4000)
    , (3, 10 , 'ELITE', 'Jones', 'Amber', 'Susan Gamb | Amber Jones', 2, 'Main', 80, 0, 1.0, 6000, 12000, 9600, 5100)
    , (4, 10 , 'ELITE', 'Jones', 'Amber', 'Susan Gamb | Amber Jones', 2, 'Main', 80, 0, 1.0, 7000, 12500, 8000, 4000)
    , (1, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 6 ,'Warehouse', 40, 40, .5, 6000, 12500, 9600, 5100)
    , (2, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 7000, 12000, 8000, 4000)
    , (3, 10 , 'ELITE', 'Jones', 'Amber',  'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 6000, 12500, 9600, 5100)
    , (4, 10 , 'ELITE', 'Jones', 'Amber',  'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 6000, 12000, 8000, 4000 )
    , (1, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown', 2 , 'Main', 60,  20, .75, 5000, 1000, 1200, 4100 )
    , (2, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown', 2, 'Main',  60, 20, .75, 4000, 1500, 1000, 3000)
    , (3, 1040 , 'ROADRUNNER', 'Brown', 'Rob', 'Paul Sugar | Rob Brown', 2, 'Main', 60, 20, .75, 4500, 2000, 1200, 4100)
    , (4, 1040 , 'ROADRUNNER', 'Brown', 'Rob', 'Paul Sugar | Rob Brown', 2, 'Main', 60, 20, .75, 4000, 1500, 1000, 3000)
    , (1, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown',  6 , 'Warehouse', 60, 20, .75, 4500, 2000, 1200, 4100)
    , (2, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4000, 1500, 1000, 3000)
    , (2, 1040 , 'ROADRUNNER',  'Brown', 'Rob', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4500, 2000, 1200, 4100 )
    , (3, 1040 , 'ROADRUNNER',  'Brown', 'Rob', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4000, 1500, 1000, 3000)
    
    )
    ,
       
           t2(PROJECT_ID, LIST, POINT_OF_CONTACT, PROJECT_NAME, BUILDING_ID, BUILDING_NAME, HOURS_USED, HOURS_TO_USE, PERCENTAGE_USED, CAPABILITY, 
           BUCKET_A, BUCKET_B, BUCKET_C, cnt) AS
        ( SELECT    PROJECT_ID, 
                    VARCHAR(NAME_FIRST CONCAT ' ' CONCAT NAME_LAST, 6000),
                    POINT_OF_CONTACT, 
                    PROJECT_NAME,
                    BUILDING_ID,
                    BUILDING_NAME,
                    HOURS_USED, 
                    HOURS_TO_USE, 
                    PERCENTAGE_USED, 
                    CAPABILITY,
                    BUCKET_A,
                    BUCKET_B,
                    BUCKET_C,
                    1
        FROM SAMPLE_DATA
                WHERE rowNum = 1
                UNION ALL
        SELECT
            t2.PROJECT_ID,
                    t2.list || ' | ' || SAMPLE_DATA.NAME_FIRST CONCAT ' ' CONCAT SAMPLE_DATA.NAME_LAST, 
                    SAMPLE_DATA.POINT_OF_CONTACT,
                    SAMPLE_DATA.PROJECT_NAME,
                    SAMPLE_DATA.BUILDING_ID,
                    SAMPLE_DATA..BUILDING_NAME,
                    SAMPLE_DATA.HOURS_USED, 
                    SAMPLE_DATA.HOURS_TO_USE, 
                    SAMPLE_DATA.PERCENTAGE_USED, 
                    SAMPLE_DATA.CAPABILITY,
                    SAMPLE_DATA.BUCKET_A, 
                    SAMPLE_DATA.BUCKET_B, 
                    SAMPLE_DATA.BUCKET_C, 
                    t2.cnt + 1
        FROM t2, SAMPLE_DATA 
        
                WHERE t2.PROJECT_ID = SAMPLE_DATA.PROJECT_ID
                AND	  t2.BUILDING_ID = SAMPLE_DATA.BUILDING_ID
                AND   t2.cnt + 1 = SAMPLE_DATA..rowNum
                 )    
        SELECT
           	        PROJECT_ID, 
                    PROJECT_NAME, 
                    POINT_OF_CONTACT,
                    BUILDING_ID,
                    BUILDING_NAME,
                    HOURS_USED, 
                    HOURS_TO_USE, 
                    PERCENTAGE_USED, 
                    CAPABILITY,
    		        BUCKET_A,
    		        BUCKET_B,
    		        BUCKET_C,
                    list
        FROM t2 
                WHERE ( PROJECT_ID, BUILDING_ID, cnt ) IN (
        SELECT PROJECT_ID, BUILDING_ID, MAX(rowNum)
        FROM SAMPLE_DATA
                GROUP BY PROJECT_ID, BUILDING_ID )    
                order by PROJECT_NAME

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is an example using LISTAGG aggregate function and LAG OLAP specification.

    Example 1a: Data
    Note1: I used name list of sample_data in your first post.
    Note2: I modified rownum of last two rows in your sample_data. If I shouldn't do that, I'm sorry.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
    /*****************************************************
    *** The cte was used to generate test data easily. ***
    *****************************************************/
     sample_data
    ( rownum, project_id , project_name , name_last , name_first , point_of_contact , building_id , building_name
    , hours_used , hours_to_use , percentage_used , capability , bucket_a , bucket_b , bucket_c ) AS (
    VALUES
      (1, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 2, 'Main' , 80, 0, 1.0, 6000, 12000, 9600, 5100)
    , (2, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 2 , 'Main', 80,  0, 1.0, 7000, 12500, 8000, 4000)
    , (3, 10 , 'ELITE', 'Jones', 'Amber', 'Susan Gamb | Amber Jones', 2, 'Main', 80, 0, 1.0, 6000, 12000, 9600, 5100)
    , (4, 10 , 'ELITE', 'Jones', 'Amber', 'Susan Gamb | Amber Jones', 2, 'Main', 80, 0, 1.0, 7000, 12500, 8000, 4000)
    , (1, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 6 ,'Warehouse', 40, 40, .5, 6000, 12500, 9600, 5100)
    , (2, 10 , 'ELITE', 'Gamb', 'Susan', 'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 7000, 12000, 8000, 4000)
    , (3, 10 , 'ELITE', 'Jones', 'Amber',  'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 6000, 12500, 9600, 5100)
    , (4, 10 , 'ELITE', 'Jones', 'Amber',  'Susan Gamb | Amber Jones', 6, 'Warehouse' , 40, 40, .5, 6000, 12000, 8000, 4000 )
    , (1, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown', 2 , 'Main', 60,  20, .75, 5000, 1000, 1200, 4100 )
    , (2, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown', 2, 'Main',  60, 20, .75, 4000, 1500, 1000, 3000)
    , (3, 1040 , 'ROADRUNNER', 'Brown', 'Rob', 'Paul Sugar | Rob Brown', 2, 'Main', 60, 20, .75, 4500, 2000, 1200, 4100)
    , (4, 1040 , 'ROADRUNNER', 'Brown', 'Rob', 'Paul Sugar | Rob Brown', 2, 'Main', 60, 20, .75, 4000, 1500, 1000, 3000)
    , (1, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown',  6 , 'Warehouse', 60, 20, .75, 4500, 2000, 1200, 4100)
    , (2, 1040 , 'ROADRUNNER', 'Sugar', 'Paul', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4000, 1500, 1000, 3000)
    , (3, 1040 , 'ROADRUNNER',  'Brown', 'Rob', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4500, 2000, 1200, 4100 )
    , (4, 1040 , 'ROADRUNNER',  'Brown', 'Rob', 'Paul Sugar | Rob Brown',  6, 'Warehouse', 60, 20, .75, 4000, 1500, 1000, 3000)
    )

    Example 1b: Query
    Code:
    SELECT a.project_id
         , b.project_name
         , b.point_of_contact
         , a.building_id
         , b.building_name
         , b.hours_used
         , b.hours_to_use
         , b.percentage_used
         , b.capability
         , b.bucket_a
         , b.bucket_b
         , b.bucket_c
         , VARCHAR(a.list , 50) AS list
     FROM  (SELECT project_id
                 , building_id
                 , LISTAGG( NULLIF(concat_name , lag_concat_name) , ' | ' ) AS list
             FROM  (SELECT project_id
                         , building_id
                         , name_first CONCAT ' ' CONCAT name_last         AS concat_name
                         , LAG(name_first CONCAT ' ' CONCAT name_last , 1 , '')
                              OVER(PARTITION BY project_id , building_id
                                       ORDER BY name_first , name_last  ) AS lag_concat_name
                     FROM  sample_data
                   )
             GROUP BY
                   project_id
                 , building_id
           ) AS a
     INNER JOIN
           (SELECT s.*
                 , ROW_NUMBER()
                      OVER(PARTITION BY project_id , building_id
                               ORDER BY rownum DESC             ) AS rownum_desc
             FROM  sample_data AS s
           ) AS b
      ON   b.project_id  = a.project_id
       AND b.building_id = a.building_id
       AND b.rownum_desc = 1
     ORDER BY
           project_name
    ;

    Example 1c: Result
    Code:
    ------------------------------------------------------------------------------
    
    PROJECT_ID  PROJECT_NAME POINT_OF_CONTACT         BUILDING_ID BUILDING_NAME HOURS_USED  HOURS_TO_USE PERCENTAGE_USED CAPABILITY  BUCKET_A    BUCKET_B    BUCKET_C    LIST                                              
    ----------- ------------ ------------------------ ----------- ------------- ----------- ------------ --------------- ----------- ----------- ----------- ----------- --------------------------------------------------
             10 ELITE        Susan Gamb | Amber Jones           2 Main                   80            0            1.00        7000       12500        8000        4000 Amber Jones | Susan Gamb                          
             10 ELITE        Susan Gamb | Amber Jones           6 Warehouse              40           40            0.50        6000       12000        8000        4000 Amber Jones | Susan Gamb                          
           1040 ROADRUNNER   Paul Sugar | Rob Brown             2 Main                   60           20            0.75        4000        1500        1000        3000 Paul Sugar | Rob Brown                            
           1040 ROADRUNNER   Paul Sugar | Rob Brown             6 Warehouse              60           20            0.75        4000        1500        1000        3000 Paul Sugar | Rob Brown                            
    
      4 record(s) selected.

Posting Permissions

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