Results 1 to 15 of 15
  1. #1
    Join Date
    Apr 2010
    Posts
    32

    Unanswered: Recursive CTE - Display codes by list in the same row

    Hello,

    I'm having difficulty writing a query to display a shipping address list of codes on the same line.

    Here's the data. note, i put the rank and row number below (last 2 columns) just to show how the anchor looks in the query. it is not actually in the data.
    Code:
    bt1          st1          AC          1          1
    bt1          st1          AG          1          2
    bt1          st1          AOC         1          3
    bt1          st2          AC          2          1
    bt1          st2          AG          2          2
    bt1          st2          HOC         2          3
    bt1          st2          HSN         2          4

    The business requirements state it must be comma separated grouped by shipping address.

    Code:
    bt1          st1          AC, AG, AOC
    bt1          st2          AC, AG, HOC, HSN
    I got it to work with a single ship id, but i need to loop through all the ship ids that belong under the same bill id. each billids can have multiple shipids, and each shipids can have multiple codes. i'm not sure how to get the 2nd row above.

    here's the query:
    Code:
    WITH cte1 (billid, shipid, code, rowrank, rownum) AS ( 
      SELECT 
        bill_id, 
        ship_id, 
        code, 
        DENSE_RANK() OVER(PARTITION BY bill_id ORDER BY st_id), 
        ROW_NUMBER() OVER(PARTITION BY ship_id ORDER BY code) 
      FROM billto 
      INNER JOIN shipto         ON bill_id=ship_bill_id 
      INNER JOIN shipto_pmcodes ON st_id=spm_st_id 
      WHERE ship_bill_id=314164161 
      GROUP BY bill_id, ship_id, code 
    ), cte2 (billid2, shipid2, list, rnk, cnt) AS ( 
      SELECT 
        billid, shipid, VARCHAR('', 256), 1, 0 
      FROM cte1 
      WHERE rowrank=1 AND rownum=1 
      UNION ALL 
      SELECT 
        billid, shipid, 
        CASE 
          WHEN cte2.list='' 
          THEN RTRIM(CHAR(cte1.code)) 
          ELSE cte2.list || ', ' || RTRIM(CHAR(cte1.code)) 
        END, 
        cte2.rnk, 
        cte2.cnt + 1 
      FROM cte1, cte2 
      WHERE cte1.rowrank=cte2.rnk AND cte1.rownum=cte2.cnt + 1 AND cte2.cnt <= 30 
    ) ( 
      SELECT billname, shipname, list 
      FROM cte2 
      INNER JOIN shipto ON shipid2=st_id 
      INNER JOIN billto ON ship_bill_id=bill_id 
      WHERE cte2.cnt = (SELECT MAX(cte2.cnt) FROM cte2) 
      FETCH FIRST 40 ROW ONLY 
    ) WITH UR;
    I tried to replace this..
    Code:
        cte2.rnk, 
        cte2.cnt + 1
    ..with this..
    Code:
    , 
        CASE 
          WHEN cte2.cnt + 1 IS NULL
          THEN cte2.rnk + 1
          ELSE cte2.rnk
        END, 
        cte2.cnt + 1
    ..but the result is the same. it won't advance to the next shipid. Somehow I need it to continue onto the next shipid after all the codes are exhausted for the current shipid.

    OS AIX
    DB2 v8.1.1.157", "special_26088", "U823673_26088", and FixPak "17".
    type unknown (permission denied on db2licm -l)

    Anyone have an idea how I can achieve this?

    (edit, i don't have authority to do anything but SELECT, so no stored procedures, custom functions, etc can be used.)
    Last edited by DonQuixote; 05-14-14 at 15:37.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You ought to be able to use this as a template:
    Code:
    CREATE TABLE foo (
       billId       VARCHAR(9)
    ,  shipId       VARCHAR(9)
    ,  wombat       VARCHAR(9)
    )
    
    INSERT INTO foo (billId, shipId, wombat) VALUES
       ('bt1', 'st1', 'AC'),   ('bt1', 'st1', 'AG')
    ,  ('bt1', 'st1', 'AOC'),  ('bt1', 'st2', 'AC')
    ,  ('bt1', 'st2', 'AG'),   ('bt1', 'st2', 'HOC')
    ,  ('bt1', 'st2', 'HSN')
    
    ; WITH c1 AS (
    SELECT billId, shipId, wombat
    ,  Row_Number() OVER (PARTITION BY billId, shipId ORDER BY wombat DESC) AS c2n
    ,  Row_Number() OVER (PARTITION BY billId, shipId ORDER BY wombat     ) AS c1n
       FROM foo
    ), c2 AS (
    SELECT a.billId, a.shipId
    ,  Cast(a.wombat AS VARCHAR(999)) AS wombat
    ,  a.c2n
       FROM c1 AS a
       WHERE 1 = c1n
    UNION ALL SELECT b.billId, b.shipId
    ,  Cast(c.wombat || ', ' || b.wombat AS VARCHAR(999)) AS wombat
    ,  b.c2n
       FROM c1 AS b
       JOIN c2 AS c
          ON  b.c2n + 1 = c.c2n
          AND b.billId  = c.billId
          AND b.shipId  = c.shipId
    )
    SELECT *
       FROM c2
       WHERE  1 = c2n
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    rowrank=1 might be unnecessary.

    Code:
    ...
    ), cte2 (billid2, shipid2, list, rnk, cnt) AS ( 
      SELECT 
        billid, shipid, VARCHAR('', 256), 1, 0 
      FROM cte1 
      WHERE rowrank=1 AND rownum=1
      UNION ALL 
    ...
    If you revised final select like this way...
    Code:
    SELECT billname
         , shipname
         , list
     FROM  cte2 AS t_a
    ...
    ...
     WHERE t_a.cnt
           = (SELECT MAX(cnt)
               FROM  cte2 AS t_b
               WHERE t_b.shipid = t_a.shipid
             )
    Last edited by tonkuma; 05-14-14 at 04:38. Reason: Add "If you revised final select like this way..." and following sample code.

  4. #4
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by DonQuixote View Post
    Hello,

    I'm having difficulty writing a query to display a shipping address list of codes on the same line.

    Here's the data. note, i put the rank and row number below (last 2 columns) just to show how the anchor looks in the query. it is not actually in the data.
    Code:
    bt1          st1          AC          1          1
    bt1          st1          AG          1          2
    bt1          st1          AOC         1          3
    bt1          st2          AC          2          1
    bt1          st2          AG          2          2
    bt1          st2          HOC         2          3
    bt1          st2          HSN         2          4

    The business requirements state it must be comma separated grouped by shipping address.

    Code:
    bt1          st1          AC, AG, AOC
    bt1          st2          AC, AG, HOC, HSN
    I got it to work with a single ship id, but i need to loop through all the ship ids that belong under the same bill id. each billids can have multiple shipids, and each shipids can have multiple codes. i'm not sure how to get the 2nd row above.

    here's the query:
    Code:
    WITH cte1 (billid, shipid, code, rowrank, rownum) AS ( 
      SELECT 
        bill_id, 
        ship_id, 
        code, 
        DENSE_RANK() OVER(PARTITION BY bill_id ORDER BY st_id), 
        ROW_NUMBER() OVER(PARTITION BY ship_id ORDER BY code) 
      FROM billto 
      INNER JOIN shipto         ON bill_id=ship_bill_id 
      INNER JOIN shipto_pmcodes ON st_id=spm_st_id 
      WHERE ship_bill_id=314164161 
        AND st_active_rec='A' AND spm_active_rec='A' 
      GROUP BY bill_id, ship_id, code 
    ), cte2 (billid2, shipid2, list, rnk, cnt) AS ( 
      SELECT 
        billid, shipid, VARCHAR('', 256), 1, 0 
      FROM cte1 
      WHERE rowrank=1 AND rownum=1 
      UNION ALL 
      SELECT 
        billid, shipid, 
        CASE 
          WHEN cte2.list='' 
          THEN RTRIM(CHAR(cte1.code)) 
          ELSE cte2.list || ', ' || RTRIM(CHAR(cte1.code)) 
        END, 
        cte2.rnk, 
        cte2.cnt + 1 
      FROM cte1, cte2 
      WHERE cte1.rowrank=cte2.rnk AND cte1.rownum=cte2.cnt + 1 AND cte2.cnt <= 30 
    ) ( 
      SELECT billname, shipname, list 
      FROM cte2 
      INNER JOIN shipto ON shipid2=st_id 
      INNER JOIN billto ON ship_bill_id=bill_id 
      WHERE cte2.cnt = (SELECT MAX(cte2.cnt) FROM cte2) 
      FETCH FIRST 40 ROW ONLY 
    ) WITH UR;
    I tried to replace this..
    Code:
        cte2.rnk, 
        cte2.cnt + 1
    ..with this..
    Code:
    , 
        CASE 
          WHEN cte2.cnt + 1 IS NULL
          THEN cte2.rnk + 1
          ELSE cte2.rnk
        END, 
        cte2.cnt + 1
    ..but the result is the same. it won't advance to the next shipid. Somehow I need it to continue onto the next shipid after all the codes are exhausted for the current shipid.

    OS AIX
    DB2 v8.1.1.157", "special_26088", "U823673_26088", and FixPak "17".
    type unknown (permission denied on db2licm -l)

    Anyone have an idea how I can achieve this?

    (edit, i don't have authority to do anything but SELECT, so no stored procedures, custom functions, etc can be used.)
    If you're on 9.7 or above you might want to have a look at LISTAGG:

    IBM Knowledge Center
    --
    Lennart

  5. #5
    Join Date
    Apr 2010
    Posts
    32
    Quote Originally Posted by Pat Phelan View Post
    You ought to be able to use this as a template:
    Code:
    ; WITH c1 AS (
    SELECT billId, shipId, wombat
    ,  Row_Number() OVER (PARTITION BY billId, shipId ORDER BY wombat DESC) AS c2n
    ,  Row_Number() OVER (PARTITION BY billId, shipId ORDER BY wombat     ) AS c1n
       FROM foo
    ), c2 AS (
    SELECT a.billId, a.shipId
    ,  Cast(a.wombat AS VARCHAR(999)) AS wombat
    ,  a.c2n
       FROM c1 AS a
       WHERE 1 = c1n
    UNION ALL SELECT b.billId, b.shipId
    ,  Cast(c.wombat || ', ' || b.wombat AS VARCHAR(999)) AS wombat
    ,  b.c2n
       FROM c1 AS b
       JOIN c2 AS c
          ON  b.c2n + 1 = c.c2n
          AND b.billId  = c.billId
          AND b.shipId  = c.shipId
    )
    SELECT *
       FROM c2
       WHERE  1 = c2n
    -PatP
    This works! i'm still not sure how though i'm trying to work through it. thanks so much!

    Quote Originally Posted by tonkuma View Post
    rowrank=1 might be unnecessary.

    Code:
    ...
    ), cte2 (billid2, shipid2, list, rnk, cnt) AS ( 
      SELECT 
        billid, shipid, VARCHAR('', 256), 1, 0 
      FROM cte1 
      WHERE rowrank=1 AND rownum=1
      UNION ALL 
    ...
    If you revised final select like this way...
    Code:
    SELECT billname
         , shipname
         , list
     FROM  cte2 AS t_a
    ...
    ...
     WHERE t_a.cnt
           = (SELECT MAX(cnt)
               FROM  cte2 AS t_b
               WHERE t_b.shipid = t_a.shipid
             )
    I tried that first but what happened was i would get the same shipto row duplicated 4 or more times, sometimes with different codes. that one condition solved it for that row. After applying your next piece, the result was the same, a single row only.

    Quote Originally Posted by lelle12 View Post
    If you're on 9.7 or above you might want to have a look at LISTAGG:

    IBM Knowledge Center
    thanks for the suggestion. LISTAGG was on my radar. Unfortunately i'm on an older db2 version. 8.1 in dev or 9.5 in production.


    Pat, i'm trying to work through this logic. It works for a single billid, but if i then wanted to include several billids, perhaps all belonging to the same account, i can't find where to adjust it so that it works. no matter what i change, i get 0 rows.

    such as..

    Code:
    AND billid=1234
    to
    AND bill_acctid=56781234
    .. results in 0 rows, even though isolating the first table just yields a larger resultset of the same data. how much will i have to change to expand this to include several billids?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by DonQuixote View Post
    Code:
    ; WITH c1 AS (
    SELECT billId, shipId, wombat
    ,  Row_Number() OVER (PARTITION BY billId, shipId ORDER BY wombat DESC) AS c2n
    ,  Row_Number() OVER (PARTITION BY billId, shipId ORDER BY wombat     ) AS c1n
       FROM foo
    ), c2 AS (
    SELECT a.billId, a.shipId
    ,  Cast(a.wombat AS VARCHAR(999)) AS wombat
    ,  a.c2n
       FROM c1 AS a
       WHERE 1 = c1n
    UNION ALL SELECT b.billId, b.shipId
    ,  Cast(c.wombat || ', ' || b.wombat AS VARCHAR(999)) AS wombat
    ,  b.c2n
       FROM c1 AS b
       JOIN c2 AS c
          ON  b.c2n + 1 = c.c2n
          AND b.billId  = c.billId
          AND b.shipId  = c.shipId
    )
    SELECT *
       FROM c2
       WHERE  1 = c2n
    -PatP

    ...
    This works! i'm still not sure how though i'm trying to work through it. thanks so much!

    ...
    Rally "This works!"?

    I got error with the query, like...
    Code:
    SQL0343N  The column names are required for the recursive common table 
    expression "DB2ADMIN.C2".  SQLSTATE=42908
    According to the error message and further trial,
    (at least) the code should be amended to meet the DB2 syntax, like...
    Code:
    ...
    ), c2
    ( billId , shipId , wombat , c2n) AS (
    SELECT a.billId, a.shipId
    ...
       FROM c1 AS b
       /*JOIN*/ , c2 AS c
       /*ON*/ WHERE b.c2n + 1 = c.c2n
          AND b.billId  = c.billId
          AND b.shipId  = c.shipId
    ...

  7. #7
    Join Date
    Apr 2010
    Posts
    32
    Quote Originally Posted by tonkuma View Post
    Rally "This works!"?

    I got error with the query, like...
    Code:
    SQL0343N  The column names are required for the recursive common table 
    expression "DB2ADMIN.C2".  SQLSTATE=42908
    According to the error message and further trial,
    (at least) the code should be amended to meet the DB2 syntax, like...
    Code:
    ...
    ), c2
    ( billId , shipId , wombat , c2n) AS (
    SELECT a.billId, a.shipId
    ...
       FROM c1 AS b
       /*JOIN*/ , c2 AS c
       /*ON*/ WHERE b.c2n + 1 = c.c2n
          AND b.billId  = c.billId
          AND b.shipId  = c.shipId
    ...
    Actually, i wrote it exactly as you did by habit before i first ran it, so i didn't notice it. Though usually leaving out the field aliases works, I guess there's something weird about recursive ctes in db2, similar to how explicit joins aren't allowed in them either, or so the errors I've received are. i just make them implicit only in recursive ctes to calm the db2 gods

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Add any additional criteria at the end of the code that I provided, after the line that reads
    Code:
    WHERE 1 = c2n
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by DonQuixote View Post
    ...

    Quote Originally Posted by tonkuma View Post
    rowrank=1 might be unnecessary.

    Code:
    ...
    ), cte2 (billid2, shipid2, list, rnk, cnt) AS ( 
      SELECT 
        billid, shipid, VARCHAR('', 256), 1, 0 
      FROM cte1 
      WHERE rowrank=1 AND rownum=1
      UNION ALL 
    ...
    If you revised final select like this way...
    Code:
    SELECT billname
         , shipname
         , list
     FROM  cte2 AS t_a
    ...
    ...
     WHERE t_a.cnt
           = (SELECT MAX(cnt)
               FROM  cte2 AS t_b
               WHERE t_b.shipid = t_a.shipid
             )
    I tried that first but what happened was i would get the same shipto row duplicated 4 or more times, sometimes with different codes. that one condition solved it for that row. After applying your next piece, the result was the same, a single row only.

    ...
    Please publish your exact tried code.

    Here is my tested code and result.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     cte1
    (billid, shipid, code, rownum) AS ( 
    /*
      SELECT bill_id
           , ship_id
           , code
           , ROW_NUMBER() OVER(PARTITION BY ship_id ORDER BY code) 
      FROM billto 
      INNER JOIN shipto         ON bill_id = ship_bill_id 
      INNER JOIN shipto_pmcodes ON st_id   = spm_st_id 
      WHERE ship_bill_id=314164161
       AND  st_active_rec  = 'A'
       AND  spm_active_rec = 'A' 
    */
    VALUES
      ( 'bt1' , 'st1' , 'AC'  , 1 )
    , ( 'bt1' , 'st1' , 'AG'  , 2 )
    , ( 'bt1' , 'st1' , 'AOC' , 3 )
    , ( 'bt1' , 'st2' , 'AC'  , 1 )
    , ( 'bt1' , 'st2' , 'AG'  , 2 )
    , ( 'bt1' , 'st2' , 'HOC' , 3 )
    , ( 'bt1' , 'st2' , 'HSN' , 4 )
    )
    , cte2
    (billid, shipid, list, cnt) AS ( 
    SELECT billid, shipid, VARCHAR('', 256), 0 
     FROM  cte1
     WHERE rownum=1 
    UNION ALL 
    SELECT cte2.billid
         , cte2.shipid
         , CASE 
           WHEN cte2.list = '' 
           THEN RTRIM(CHAR(cte1.code)) 
           ELSE cte2.list || ', ' || RTRIM(CHAR(cte1.code)) 
           END
         , cte2.cnt + 1 
      FROM cte2
         , cte1 
     WHERE cte1.shipid = cte2.shipid
      AND  cte1.rownum = cte2.cnt + 1
      AND  cte2.cnt <= 30 
    )
    SELECT /*billname, shipname*/
           billid, shipid
         , list
     FROM  cte2 AS t_a
    /*
     INNER JOIN
           shipto
      ON   st_id = shipid
     INNER JOIN
           billto
      ON   bill_id = ship_bill_id
    */
     WHERE t_a.cnt
           = (SELECT MAX(t_b.cnt)
               FROM  cte2 AS t_b
               WHERE t_b.shipid = t_a.shipid
             )
     FETCH FIRST 40 ROW ONLY
    WITH UR
    ;
    ------------------------------------------------------------------------------
    
    BILLID SHIPID LIST                                                                                                                                                                                                                                                            
    ------ ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    bt1    st1    AC, AG, AOC                                                                                                                                                                                                                                                     
    bt1    st2    AC, AG, HOC, HSN                                                                                                                                                                                                                                                
    
      2 record(s) selected.

    ... i would get the same shipto row duplicated 4 or more times, sometimes with different codes.
    You may want to add conditions between billId(same as Pat's example), like...
    Code:
    ...
      FROM cte2
         , cte1 
     WHERE cte1.shipid = cte2.shipid
      AND  cte1.billId = cte2.billId
      AND  cte1.rownum = cte2.cnt + 1
      AND  cte2.cnt <= 30 
    )
    SELECT /*billname, shipname*/
           billid, shipid
         , list
     FROM  cte2 AS t_a
    /*
     INNER JOIN
           shipto
      ON   st_id = shipid
     INNER JOIN
           billto
      ON   bill_id = ship_bill_id
    */
     WHERE t_a.cnt
           = (SELECT MAX(t_b.cnt)
               FROM  cte2 AS t_b
               WHERE t_b.shipid = t_a.shipid
                AND  t_b.billId = t_a.billId
             )
     FETCH FIRST 40 ROW ONLY
    WITH UR
    ;

  10. #10
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    I prefer Lenny's suggestion on the LISTAGG as wll, in fact, most times for this functionality I use, XMLSERIALIZE(XMLAGG( XMLTEXT( column))AS CLOB(4000) ) as it is portable to all the platforms I support. LISTAGG is not supported on Z/OS. Here's an example of the SQL that I posted on the site here recently:
    Code:
    create view comment_tab_v (key, str) as 
    (select key, XMLSERIALIZE(       
         XMLAGG( XMLTEXT( chunk))AS CLOB(4000) )  as str
    from comment_tab 
    group by key);
    Dave

  11. #11
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by DonQuixote View Post
    [...]
    thanks for the suggestion. LISTAGG was on my radar. Unfortunately i'm on an older db2 version. 8.1 in dev or 9.5 in production.
    [...]
    Probably good if you can use the same version in dev as in production. For 9.5 I think it should be possible to use the predessor of LISTAGG, See the following blogentry by Serge/Rick:

    https://www.ibm.com/developerworks/m...ings42?lang=en
    --
    Lennart

  12. #12
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1

    Why are you smarter than Dr. Codd??

    I'm having difficulty writing a query to display a shipping address list of codes on the same line.
    Gee, Dr. Codd wasted his life with those awful Normal Forms! Display formatting is done in a presentation layer, not in a query. A query has only scalar values in the columns. This is called "First Normal Form" or 1NF and it is the foundation of all RDBMS.

  13. #13
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    Actually, SQL has turned into the presentation layer for a lot of places nowadays. The database is still relational, however, SQL has become a quite encompassing procedural language.
    Dave

  14. #14
    Join Date
    Apr 2010
    Posts
    32
    Quote Originally Posted by tonkuma View Post
    Please publish your exact tried code.

    Here is my tested code and result.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     cte1
    (billid, shipid, code, rownum) AS ( 
    /*
      SELECT bill_id
           , ship_id
           , code
           , ROW_NUMBER() OVER(PARTITION BY ship_id ORDER BY code) 
      FROM billto 
      INNER JOIN shipto         ON bill_id = ship_bill_id 
      INNER JOIN shipto_pmcodes ON st_id   = spm_st_id 
      WHERE ship_bill_id=314164161
       AND  st_active_rec  = 'A'
       AND  spm_active_rec = 'A' 
    */
    VALUES
      ( 'bt1' , 'st1' , 'AC'  , 1 )
    , ( 'bt1' , 'st1' , 'AG'  , 2 )
    , ( 'bt1' , 'st1' , 'AOC' , 3 )
    , ( 'bt1' , 'st2' , 'AC'  , 1 )
    , ( 'bt1' , 'st2' , 'AG'  , 2 )
    , ( 'bt1' , 'st2' , 'HOC' , 3 )
    , ( 'bt1' , 'st2' , 'HSN' , 4 )
    )
    , cte2
    (billid, shipid, list, cnt) AS ( 
    SELECT billid, shipid, VARCHAR('', 256), 0 
     FROM  cte1
     WHERE rownum=1 
    UNION ALL 
    SELECT cte2.billid
         , cte2.shipid
         , CASE 
           WHEN cte2.list = '' 
           THEN RTRIM(CHAR(cte1.code)) 
           ELSE cte2.list || ', ' || RTRIM(CHAR(cte1.code)) 
           END
         , cte2.cnt + 1 
      FROM cte2
         , cte1 
     WHERE cte1.shipid = cte2.shipid
      AND  cte1.rownum = cte2.cnt + 1
      AND  cte2.cnt <= 30 
    )
    SELECT /*billname, shipname*/
           billid, shipid
         , list
     FROM  cte2 AS t_a
    /*
     INNER JOIN
           shipto
      ON   st_id = shipid
     INNER JOIN
           billto
      ON   bill_id = ship_bill_id
    */
     WHERE t_a.cnt
           = (SELECT MAX(t_b.cnt)
               FROM  cte2 AS t_b
               WHERE t_b.shipid = t_a.shipid
             )
     FETCH FIRST 40 ROW ONLY
    WITH UR
    ;
    ------------------------------------------------------------------------------
    
    BILLID SHIPID LIST                                                                                                                                                                                                                                                            
    ------ ------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    bt1    st1    AC, AG, AOC                                                                                                                                                                                                                                                     
    bt1    st2    AC, AG, HOC, HSN                                                                                                                                                                                                                                                
    
      2 record(s) selected.


    You may want to add conditions between billId(same as Pat's example), like...
    Code:
    ...
      FROM cte2
         , cte1 
     WHERE cte1.shipid = cte2.shipid
      AND  cte1.billId = cte2.billId
      AND  cte1.rownum = cte2.cnt + 1
      AND  cte2.cnt <= 30 
    )
    SELECT /*billname, shipname*/
           billid, shipid
         , list
     FROM  cte2 AS t_a
    /*
     INNER JOIN
           shipto
      ON   st_id = shipid
     INNER JOIN
           billto
      ON   bill_id = ship_bill_id
    */
     WHERE t_a.cnt
           = (SELECT MAX(t_b.cnt)
               FROM  cte2 AS t_b
               WHERE t_b.shipid = t_a.shipid
                AND  t_b.billId = t_a.billId
             )
     FETCH FIRST 40 ROW ONLY
    WITH UR
    ;
    This was it! now it works, even when i run it at the account level, to include its billtos and the billtos' shiptos.

    Code:
    WITH cte1 (billid, shipid, spmcode, rownum) AS ( 
      SELECT 
        bill_id, ship_id, spm_code, 
        ROW_NUMBER() OVER(PARTITION BY st_id ORDER BY spm_code) 
      FROM bill_to 
      INNER JOIN ship_to ON bill_id=ship_bill_id 
      LEFT OUTER JOIN shipto_pmcode ON ship_id=spm_ship_id 
      WHERE bill_acct_id=56781234 
    ), cte2 (billid2, shipid2, list, cnt) AS ( 
      SELECT 
        billid, shipid, VARCHAR('', 256), 0 
      FROM cte1 
      WHERE rownum=1 
      UNION ALL 
      SELECT 
        cte2.billid2, cte2.shipid2, 
        CASE 
          WHEN cte2.list='' 
          THEN RTRIM(CHAR(cte1.spmcode)) 
          ELSE cte2.list || ', ' || RTRIM(CHAR(cte1.spmcode)) 
        END, 
        cte2.cnt + 1 
      FROM cte1, cte2 
      WHERE cte1.stid=cte2.stid2 AND cte1.rownum=cte2.cnt + 1 AND cte2.cnt <= 50 
    ) ( 
      SELECT bill_name, ship_name, TRIM(list) 
      FROM cte2 AS t1 
      INNER JOIN ship_to ON t1.shipid2=ship_id 
      INNER JOIN bill_to ON ship_bill_id=bill_id 
      WHERE t1.cnt = (SELECT MAX(t2.cnt) FROM cte2 AS t2 WHERE t1.shipid2=t2.shipid2) 
      ORDER BY bill_name, ship_name 
      FETCH FIRST 500 ROW ONLY 
    ) WITH UR;
    I need to confess that I'm still confused as to how these changes work. I can get what I need now using either this or the seemingly simpler methods below, but the ultimate goal is to learn how to visualize this in the event a similar requirement surfaces. I've gone through a few guides on recursive CTEs but hopefully it'll sink in soon. I'll keep learning about it. thanks tremendously for helping with this.

    Quote Originally Posted by dav1mo View Post
    I prefer Lenny's suggestion on the LISTAGG as wll, in fact, most times for this functionality I use, XMLSERIALIZE(XMLAGG( XMLTEXT( column))AS CLOB(4000) ) as it is portable to all the platforms I support. LISTAGG is not supported on Z/OS. Here's an example of the SQL that I posted on the site here recently:
    Code:
    create view comment_tab_v (key, str) as 
    (select key, XMLSERIALIZE(       
         XMLAGG( XMLTEXT( chunk))AS CLOB(4000) )  as str
    from comment_tab 
    group by key);
    Quote Originally Posted by lelle12 View Post
    Probably good if you can use the same version in dev as in production. For 9.5 I think it should be possible to use the predessor of LISTAGG, See the following blogentry by Serge/Rick:

    https://www.ibm.com/developerworks/m...ings42?lang=en

    dav1mo, do views persist, or do they last only as long as the query? i don't think they'll allow me to create anything, certainly not permanent. If it doesn't persist after the query is finished running, i'll take a good look at this or at lelle12s' as they both look really similar. thanks to both.

    Quote Originally Posted by Celko View Post
    Gee, Dr. Codd wasted his life with those awful Normal Forms! Display formatting is done in a presentation layer, not in a query. A query has only scalar values in the columns. This is called "First Normal Form" or 1NF and it is the foundation of all RDBMS.
    Celko, i appreciate the perspective. No data is being stored in such a fashion. Though I fought against running it this way, i lost and i'd rather not have to write a separate script to massage the data. I'd like to give birth to the feed in adult form straight out of the womb.

    Cheers and thanks to everyone here.

  15. #15
    Join Date
    Apr 2010
    Posts
    32
    Quote Originally Posted by lelle12 View Post
    Probably good if you can use the same version in dev as in production. For 9.5 I think it should be possible to use the predessor of LISTAGG, See the following blogentry by Serge/Rick:

    https://www.ibm.com/developerworks/m...ings42?lang=en
    oh god, i've wasted like 2 days and i think i spent like 2 minutes on this to get the same exact result. I think i'll take the rest of the day off. can't find a suitable smilie for this. thanks for the link.

Posting Permissions

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