Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Join Date
    Aug 2011
    Posts
    25

    Lightbulb Unanswered: Need help with a Query

    Hi!

    I have a question to a query I wish to create.
    I attached Table and query to txt file to format it a bit better, hope thats ok.

    I have a Table with some cmds, that I want to bring into the right order. Dependency for one cmd is stored in column dependency.
    I created a query (see text file).
    I get quite a good output for my skills, but I am missing special cmds, that have dependency like ..._all (this is hardcoded). They are missing in my result and I don't know how to add them to my query.

    Can anyone take a look at my problem?

    Thanks in advance!


    Forgot to say... It's DB2 ;-)
    Attached Files Attached Files

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What do you mean by "cmd"?
    I couldn't see the word or column of that name in your attached file.

    What is a datatype of dependency?
    Is it VARCHAR?

    What output do you want for id = 22?

    What columns do you want to include in your output?
    All of id, depemdency, type, path, and depth?

    Please don't use tab characters to format in your file or posted message.
    Tab setting may be different by each environment.

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I made a test data from your text file like the following.
    Is it right?
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     run(id , dependency , type) AS (
    VALUES
      ( 1 , '33'    , 'T_ALL')
    , ( 4 , '33'    , 'T_ALL')
    , (33 , '55'    , ''     )
    , (78 , ''      , ''     )
    , (23 , '78'    , ''     )
    , (38 , '23'    , ''     )
    , (22 , 'T_ALL' , ''     )
    , (68 , ''      , ''     )
    , (55 , ''      , ''     )
    , (18 , ''      , ''     )
    , (97 , '18'    , ''     )
    )
    SELECT * FROM run
    ;
    ------------------------------------------------------------------------------
    
    ID          DEPENDENCY TYPE 
    ----------- ---------- -----
              1 33         T_ALL
              4 33         T_ALL
             33 55              
             78                 
             23 78              
             38 23              
             22 T_ALL           
             68                 
             55                 
             18                 
             97 18              
    
      11 record(s) selected.

  4. #4
    Join Date
    Aug 2011
    Posts
    25
    Quote Originally Posted by tonkuma View Post
    What do you mean by "cmd"?
    I couldn't see the word or column of that name in your attached file.

    What is a datatype of dependency?
    Is it VARCHAR?

    What output do you want for id = 22?

    What columns do you want to include in your output?
    All of id, depemdency, type, path, and depth?

    Please don't use tab characters to format in your file or posted message.
    Tab setting may be different by each environment.
    Ah sorry, I mean the ID. Behind the ID stands a special Commandjob but thats irrelevant for this.

    Datatype of dependency is Character (100) nullable, of ID is bigint and type varchar(100)

    For id 22 I would like the following Line (should be in this case at the end, but theoreticly could also be in the middle):

    ID:22
    Dependency:T_ALL
    Type: T_ALL
    Path:55,33,1,4,22
    Depth:4

    But: I just played around with the path, I don't need it forcefully. It would be enough to have the IDs in the right order for the beginning.

    Hope it is understandable what I mean ;-)

    Thanks!



    Edit: you are fast :-) yes, thats exactly right!

  5. #5
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by odin568 View Post
    Edit: you are fast :-) yes, thats exactly right!
    Yeah, next time you've got a SQL question try to post it during the japanese night.... maybe we get a chance....

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Although I have some questions that I wrote at the bottom of this post,
    here is a query which returned same result in your attached file from your provided data.
    (Tested on DB2 9.7.4 on Windows/XP)
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     run(id , dependency , type) AS (
    SELECT BIGINT(id)
         , CHAR(dependency , 100)
         , VARCHAR(type    , 100)
     FROM
           (VALUES
              ( 1 , '33'    , 'T_ALL')
            , ( 4 , '33'    , 'T_ALL')
            , (33 , '55'    , ''     )
            , (78 , ''      , ''     )
            , (23 , '78'    , ''     )
            , (38 , '23'    , ''     )
            , (22 , 'T_ALL' , ''     )
            , (68 , ''      , ''     )
            , (55 , ''      , ''     )
            , (18 , ''      , ''     )
            , (97 , '18'    , ''     )
           ) r(id , dependency , type)
    )
    , r_cte(id , dependency , type , n_id , path , depth) AS (
    SELECT r.id
         , r.dependency
         , COALESCE(MAX(n.type) , '') || r.type
         , MAX(n.dependency)
         , VARCHAR( COALESCE( LISTAGG(VARCHAR(n.id) || ',') , '' ) , 20 )
           || VARCHAR(r.id)
         , 1 + SIGN( COUNT(n.id) )
        /* CASE WHEN MAX(n.id) IS NULL THEN 1 ELSE 2 END */
     FROM  run r
     LEFT  OUTER JOIN
           run n
       ON  VARCHAR(n.id) = r.dependency
       OR  r.dependency  = 'T_ALL'
       AND n.type        = 'T_ALL'
     GROUP BY
           r.id
         , r.dependency
         , r.type
    UNION ALL
    SELECT r.id
         , r.dependency
         , r.type
         , n.dependency
         , VARCHAR(n.id) || ',' || r.path
         , r.depth + 1
     FROM  r_cte r
         , run   n
     WHERE VARCHAR(n.id) = r.n_id
       AND depth < 10
    )
    SELECT SMALLINT(id)             AS id
         , VARCHAR(dependency , 10) AS dependency
         , VARCHAR(type       ,  6) AS type
         , path
         , depth
     FROM  r_cte r
     WHERE NOT EXISTS
           (SELECT 0
             FROM  r_cte n
             WHERE n.id    = r.id
               AND n.depth > r.depth
           )
     ORDER BY
           depth
         , type
         , id * (depth * 2 - 3)
    ;
    ------------------------------------------------------------------------------
    
    ID     DEPENDENCY TYPE   PATH                                     DEPTH      
    ------ ---------- ------ ---------------------------------------- -----------
        78                   78                                                 1
        68                   68                                                 1
        55                   55                                                 1
        18                   18                                                 1
        23 78                78,23                                              2
        33 55                55,33                                              2
        97 18                18,97                                              2
        38 23                78,23,38                                           3
         1 33         T_ALL  55,33,1                                            3
         4 33         T_ALL  55,33,4                                            3
        22 T_ALL      T_ALL  55,33,1,4,22                                       4
    
      11 record(s) selected.
    Questions:
    1) Are there more than two rows of which dependency were 'T_ALL'?
    For example:
    Code:
    ID          DEPENDENCY TYPE 
    ----------- ---------- -----
             22 T_ALL           
            125 T_ALL
    If so, what output do you expect?

    2) Are there any row of which dependency was different from other rows with type = 'T_ALL'?
    For example:
    Code:
    ID          DEPENDENCY TYPE 
    ----------- ---------- -----
              1 33         T_ALL
              4 33         T_ALL
              5 35         T_ALL
    If so, what output do you expect?

    3) What order of rows do you want in your result?
    In your sample result,
    rows id = 23 and 33 were reverse order of your sample table.
    Last edited by tonkuma; 08-23-11 at 00:09. Reason: Changed question 3)

  7. #7
    Join Date
    Aug 2011
    Posts
    25

    Lightbulb

    Hi!

    Thanks very much in advance for your effort!
    Sorry, my description yesterday was not too good, was in a hurry... try to make it better now :-)

    Q1: Yes, this can happen. In your example the IDs 22 and 125 can only start if all IDs which have the Type T_ALL had run.
    But in this case, 22 and 125 are independent from each other. So they can start after all T_ALL have finished. So the order is not relevant.

    Q2: Yes also this could happen. Then I would expect, that 1 and 4 can only run if 33 had run and 5 can only start if 35 had run. After that in this example all IDs with Dependency T_ALL can start

    Q3: Well, the order is not too important. If two IDs are on the same "level", i.e. have the same dependency, then It doesn't matter which comes first. Perhaps it would be nice to to sort it from the id.

    Things I also forgot to say:

    I'm running DB2/AIX64 9.1.6 so unfortunately the functions VARCHAR doesn't work (use char() instead solves) and LISTAGG() doesn't exist At least it says:
    No authorized routine named "LISTAGG" of type "FUNCTION
    No authorized routine named "VARCHAR" of type "FUNCTION

    I appreceate your help, so thanks in advance for your effort!!

  8. #8
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    LISTAGG is only in 9.7
    varchar was available : some other reason for this (incorrect syntax.. incorrect value....
    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

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm running DB2/AIX64 9.1.6
    So, VARCHAR can be replaced by RTRIM(CHAR(...)) like in your query.
    LISTAGG can be rewritten by using XMLSERIALIZE and XMLAGG.

    Here is an example.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     run(id , dependency , type) AS (
    SELECT BIGINT(id)
         , CHAR(dependency , 100)
         , VARCHAR(type    , 100)
     FROM
           (VALUES
              ( 1 , '33'    , 'T_ALL')
            , ( 4 , '33'    , 'T_ALL')
            , (33 , '55'    , ''     )
            , (78 , ''      , ''     )
            , (23 , '78'    , ''     )
            , (38 , '23'    , ''     )
            , (22 , 'T_ALL' , ''     )
            , (68 , ''      , ''     )
            , (55 , ''      , ''     )
            , (18 , ''      , ''     )
            , (97 , '18'    , ''     )
           ) r(id , dependency , type)
    )
    , r_cte(id , dependency , type , n_id , path , depth) AS (
    SELECT r.id
         , r.dependency
         , COALESCE(MAX(n.type) , '') || r.type
         , MAX(n.dependency)
         , REPLACE( REPLACE(
              COALESCE(
                 XMLSERIALIZE(
                    XMLAGG( XMLELEMENT(NAME e , n.id OPTION NULL ON NULL) )
                    AS VARCHAR(30) )
               , '' )
            , '<E>' , '' ) , '</E>' , ',' )
           || RTRIM( CHAR(r.id) )
         , 1 + SIGN( COUNT(n.id) )
        /* CASE WHEN MAX(n.id) IS NULL THEN 1 ELSE 2 END */
     FROM  run r
     LEFT  OUTER JOIN
           run n
       ON  CHAR(n.id) = r.dependency
       OR  r.dependency  = 'T_ALL'
       AND n.type        = 'T_ALL'
     GROUP BY
           r.id
         , r.dependency
         , r.type
    UNION ALL
    SELECT r.id
         , r.dependency
         , r.type
         , n.dependency
         , RTRIM( CHAR(n.id) ) || ',' || r.path
         , r.depth + 1
     FROM  r_cte r
         , run   n
     WHERE CHAR(n.id) = r.n_id
       AND depth < 10
    )
    SELECT SMALLINT(id)             AS id
         , VARCHAR(dependency , 10) AS dependency
         , VARCHAR(type       ,  6) AS type
         , path
         , depth
     FROM  r_cte r
     WHERE NOT EXISTS
           (SELECT 0
             FROM  r_cte n
             WHERE n.id    = r.id
               AND n.depth > r.depth
           )
     ORDER BY
           depth
         , type
    ;
    ------------------------------------------------------------------------------
    
    ID     DEPENDENCY TYPE   PATH                                               DEPTH      
    ------ ---------- ------ -------------------------------------------------- -----------
        18                   18                                                           1
        55                   55                                                           1
        68                   68                                                           1
        78                   78                                                           1
        23 78                78,23                                                        2
        33 55                55,33                                                        2
        97 18                18,97                                                        2
        38 23                78,23,38                                                     3
         1 33         T_ALL  55,33,1                                                      3
         4 33         T_ALL  55,33,4                                                      3
        22 T_ALL      T_ALL  55,33,1,4,22                                                 4
    
      11 record(s) selected.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Q1: Yes, this can happen. In your example the IDs 22 and 125 can only start if all IDs which have the Type T_ALL had run.
    But in this case, 22 and 125 are independent from each other. So they can start after all T_ALL have finished. So the order is not relevant.

    Q2: Yes also this could happen. Then I would expect, that 1 and 4 can only run if 33 had run and 5 can only start if 35 had run. After that in this example all IDs with Dependency T_ALL can start
    I couldn't understand exactly.

    If the data was like the following, what result do you expect?
    Code:
    SELECT * FROM run;
    ------------------------------------------------------------------------------
    
    ID          DEPENDENCY TYPE 
    ----------- ---------- -----
              1 33         T_ALL
              4 33         T_ALL
              5 35         T_ALL
             33 55              
             35                 
             78                 
             23 78              
             38 23              
             22 T_ALL           
             68                 
             55                 
             18                 
             97 18              
            125 T_ALL           
    
      14 record(s) selected.
    I thought the result might be like the following.

    But, I have some questions.
    Q1) Where to put 35 for id=22 and 125?
    35,55,33,1,4,5,22
    55,35,33,1,4,5,22
    55,33,35,1,4,5,22
    55,33,1,4,35,5,22

    Q2) Is it neccesary to show type = T_ALL for id=22 and 125?

    Code:
    ID          DEPENDENCY TYPE  PATH                 DEPTH
    ----------- ---------- ----- -------------------- -----
             35                  35                   1
             78                  78                   1
             68                  68                   1
             55                  55                   1
             18                  18                   1
             33 55               55,33                2
             23 78               78,23                2
             97 18               18,97                2
              5 35         T_ALL 35,5                 2
             38 23               78,23,38             3
              1 33         T_ALL 55,33,1              3
              4 33         T_ALL 55,33,4              3
             22 T_ALL      T_ALL 55,33,1,4,5,22       4   -- where to put 35?
            125 T_ALL      T_ALL 55,33,1,4,5,125      4   -- where to put 35?
    Last edited by tonkuma; 08-23-11 at 05:27. Reason: Add an assumed result.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought another design of the table.

    If you put the list of preceding ids into dependency column,
    you would be able to keep more complex command job sequencies into the run table.

    And, it would be better to make the datatype of the dependency column as VARCHAR,
    because of ease of queries and save of disk space.

    For example:
    This data in the run table represents the following job networks.
    Code:
    ID     DEPENDENCY          
    ------ --------------------
         1 33                  
         4 33                  
         5 34                  
        33 55                  
        34                     
        78                     
        23 78                  
        38 23                  
        22 1,4,5               
        68                     
        55                     
        41                     
        43 41                  
        45 43,47               
        47 41                  
        49 47                  
       101                     
       141 47,101              
       125 1,4,5               
       201 125                 
       252 125,45              
       343 45,125,141          
       490 49,343
    Code:
         55                    68                    78        
         |                                           |
         |                                           |
         V                                           V
         33          34                              23
         ||          |                               |
         |+----+     |                               |
         |     |     |                               V
         V     V     V                               38
         01    04    05              41
         ||    ||    ||              ||
         ||    ||    |+----+         |+----+
         ||    |+----|----+|         |     |
         |+----|-----|---+||         V     V
         |     |     |   |||         43    47
         |+----+     |   |||         |     |||
         ||+---------+   |||         |+----+|+---------+
         |||             |||         ||     |          |
         VVV             VVV         VV     |     101  |
         22              125         45     |     |    |
                         |||         ||     |+----+    |
                   +-----+|+-----+   ||     ||         |
                   |      |      |   ||     VV         V
                   |      |+-----|---+|     141        49
                   |      ||     |+---+     |          |
                   |      ||     ||+--------+          |
                   |      ||     |||                   |
                   V      VV     VVV                   |
                   201    252    343                   |
                                 |                     |
                                 |+--------------------+
                                 ||
                                 VV
                                 490

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Here is a trial query to get the path and depth.
    I'm sorry!
    There was a way which was easier to understand and shorter.
    I replaced all of the query example and it's result.


    Note 1: I used shorter datatypes of id and dependency, because of ease of checking the intermediate and final results in testing of queries.

    Note 2: The data was same as the previous post.

    Note 3: I tested the query on DB2 9.7.4 on Windows.
    But, you might be able to run the query on DB2 9.1 by the application of same modifications I showed in previous examples like...
    I'm running DB2/AIX64 9.1.6
    So, VARCHAR can be replaced by RTRIM(CHAR(...)) like in your query.
    LISTAGG can be rewritten by using XMLSERIALISE and XMLAGG.

    Here is an example.
    ...
    Sample query:
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     run(id , dependency) AS (
    SELECT SMALLINT(id)
         , VARCHAR(dependency , 20)
     FROM
           (VALUES
    ...
           ) r(id , dependency)
    )
    , r_cte(id , dependency , path_id , depth) AS (
    SELECT id
         , ' ,' || dependency
         , VARCHAR(id)
         , 1
     FROM  run
    UNION ALL
    SELECT r.id
         , ',' || n.dependency
         , VARCHAR(n.id)
         , depth + 1
     FROM  r_cte r
         , run   n
     WHERE LOCATE( ',' || VARCHAR(n.id) || ','
                 , r.dependency || ','
                 ) > 0
    )
    SELECT id
         , SUBSTR( MIN(dependency) , 3 ) AS dependency
         , VARCHAR(
              LISTAGG( path_id , ',' )
                 WITHIN GROUP( ORDER BY depth DESC , INT(path_id) )
            , 60
           ) AS path
         , MAX(depth) AS depth
     FROM  (SELECT r.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY id , path_id
                                ORDER BY depth DESC ) AS rn
             FROM  r_cte r
           ) r
     WHERE rn = 1
     GROUP BY
           id
     ORDER BY
           depth
    ;
    Note 4:
    A predicate using a LOCATE function in the WHERE clause can be replaced by a LIKE predicate on DB2 9.7.4 for LUW, like...
    Code:
    ...
    /*
     WHERE LOCATE( ',' || VARCHAR(n.id) || ','
                 , r.dependency || ','
                 ) > 0
    */
     WHERE r.dependency || ',' LIKE '%,' || VARCHAR(n.id) || ',%'
       AND r.depth < 10
    )
    Result of the sample query:
    Code:
    ------------------------------------------------------------------------------
    
    ID     DEPENDENCY             PATH                                                         DEPTH      
    ------ ---------------------- ------------------------------------------------------------ -----------
        34                        34                                                                     1
        41                        41                                                                     1
        55                        55                                                                     1
        68                        68                                                                     1
        78                        78                                                                     1
       101                        101                                                                    1
         5 34                     34,5                                                                   2
        23 78                     78,23                                                                  2
        33 55                     55,33                                                                  2
        43 41                     41,43                                                                  2
        47 41                     41,47                                                                  2
         1 33                     55,33,1                                                                3
         4 33                     55,33,4                                                                3
        38 23                     78,23,38                                                               3
        45 43,47                  41,43,47,45                                                            3
        49 47                     41,47,49                                                               3
       141 47,101                 41,47,101,141                                                          3
        22 1,4,5                  55,33,34,1,4,5,22                                                      4
       125 1,4,5                  55,33,34,1,4,5,125                                                     4
       201 125                    55,33,34,1,4,5,125,201                                                 5
       252 125,45                 55,33,34,41,1,4,5,43,47,45,125,252                                     5
       343 45,125,141             55,33,34,41,1,4,5,43,47,101,45,125,141,343                             5
       490 49,343                 55,33,34,41,1,4,5,43,47,101,45,125,141,49,343,490                      6
    
      23 record(s) selected.
    Last edited by tonkuma; 08-28-11 at 01:32. Reason: Change expressions for dependency to remove a scalar-subselect. Change from GROUP BY to ROW_NUMBER. Replace all of contents.

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Another example slightly diffrent in calculations of dependency.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     run(id , dependency) AS (
    SELECT SMALLINT(id)
         , VARCHAR(dependency , 20)
     FROM
           (VALUES
    ...
           ) r(id , dependency)
    )
    , r_cte(id , dependency , path_id , depth) AS (
    SELECT id
         , dependency
         , VARCHAR(id)
         , 1
     FROM  run
    UNION ALL
    SELECT r.id
         , n.dependency
         , VARCHAR(n.id)
         , depth + 1
     FROM  r_cte r
         , run   n
     WHERE LOCATE( ',' || VARCHAR(n.id) || ','
                 , ',' || r.dependency  || ','
                 ) > 0
       AND r.depth < 10
    )
    SELECT id
         , MAX( CASE depth WHEN 1 THEN dependency END ) AS dependency
         , VARCHAR(
              LISTAGG( path_id , ',' )
                 WITHIN GROUP( ORDER BY depth DESC , INT(path_id) )
            , 60
           ) AS path
         , MAX(depth) AS depth
     FROM  (SELECT r.*
                 , ROW_NUMBER()
                      OVER( PARTITION BY id , path_id
                                ORDER BY depth DESC ) AS rn
             FROM  r_cte r
           ) r
     WHERE rn = 1
     GROUP BY
           id
     ORDER BY
           depth
    ;
    Another expression for the dependency column in final select on DB2 9.7 for LUW.
    Code:
    --   , MAX( CASE depth WHEN 1 THEN dependency END ) AS dependency
         , SUBSTR( MIN( depth || dependency ) , 2 ) AS dependency
    Result: Length of dependecy column was 20(different from previous result 22.)
    Code:
    ------------------------------------------------------------------------------
    
    ID     DEPENDENCY           PATH                                                         DEPTH      
    ------ -------------------- ------------------------------------------------------------ -----------
        34                      34                                                                     1
        41                      41                                                                     1
        55                      55                                                                     1
        68                      68                                                                     1
        78                      78                                                                     1
       101                      101                                                                    1
         5 34                   34,5                                                                   2
        23 78                   78,23                                                                  2
        33 55                   55,33                                                                  2
        43 41                   41,43                                                                  2
        47 41                   41,47                                                                  2
         1 33                   55,33,1                                                                3
         4 33                   55,33,4                                                                3
        38 23                   78,23,38                                                               3
        45 43,47                41,43,47,45                                                            3
        49 47                   41,47,49                                                               3
       141 47,101               41,47,101,141                                                          3
        22 1,4,5                55,33,34,1,4,5,22                                                      4
       125 1,4,5                55,33,34,1,4,5,125                                                     4
       201 125                  55,33,34,1,4,5,125,201                                                 5
       252 125,45               55,33,34,41,1,4,5,43,47,45,125,252                                     5
       343 45,125,141           55,33,34,41,1,4,5,43,47,101,45,125,141,343                             5
       490 49,343               55,33,34,41,1,4,5,43,47,101,45,125,141,49,343,490                      6
    
      23 record(s) selected.
    Last edited by tonkuma; 08-28-11 at 04:56. Reason: Add "Another expression for the dependency..."

  14. #14
    Join Date
    Aug 2011
    Posts
    25

    Lightbulb

    Hi!

    I am very sorry, I didn't reply the last days, I was on travel and had no Internet, sorry. Wow, your support in my problem is really super, thanks a lot!

    So, I try to answer chronological to your posts:

    Quote Originally Posted by tonkuma View Post
    So, VARCHAR can be replaced by RTRIM(CHAR(...)) like in your query.
    LISTAGG can be rewritten by using XMLSERIALIZE and XMLAGG.

    Here is an example.
    Code:
    ------------------------------ Commands Entered ------------------------------
    WITH
     run(id , dependency , type) AS (
    SELECT BIGINT(id)
         , CHAR(dependency , 100)
         , VARCHAR(type    , 100)
     FROM
           (VALUES
              ( 1 , '33'    , 'T_ALL')
            , ( 4 , '33'    , 'T_ALL')
            , (33 , '55'    , ''     )
            , (78 , ''      , ''     )
            , (23 , '78'    , ''     )
            , (38 , '23'    , ''     )
            , (22 , 'T_ALL' , ''     )
            , (68 , ''      , ''     )
            , (55 , ''      , ''     )
            , (18 , ''      , ''     )
            , (97 , '18'    , ''     )
           ) r(id , dependency , type)
    )
    , r_cte(id , dependency , type , n_id , path , depth) AS (
    SELECT r.id
         , r.dependency
         , COALESCE(MAX(n.type) , '') || r.type
         , MAX(n.dependency)
         , REPLACE( REPLACE(
              COALESCE(
                 XMLSERIALIZE(
                    XMLAGG( XMLELEMENT(NAME e , n.id OPTION NULL ON NULL) )
                    AS VARCHAR(30) )
               , '' )
            , '<E>' , '' ) , '</E>' , ',' )
           || RTRIM( CHAR(r.id) )
         , 1 + SIGN( COUNT(n.id) )
        /* CASE WHEN MAX(n.id) IS NULL THEN 1 ELSE 2 END */
     FROM  run r
     LEFT  OUTER JOIN
           run n
       ON  CHAR(n.id) = r.dependency
       OR  r.dependency  = 'T_ALL'
       AND n.type        = 'T_ALL'
     GROUP BY
           r.id
         , r.dependency
         , r.type
    UNION ALL
    SELECT r.id
         , r.dependency
         , r.type
         , n.dependency
         , RTRIM( CHAR(n.id) ) || ',' || r.path
         , r.depth + 1
     FROM  r_cte r
         , run   n
     WHERE CHAR(n.id) = r.n_id
       AND depth < 10
    )
    SELECT SMALLINT(id)             AS id
         , VARCHAR(dependency , 10) AS dependency
         , VARCHAR(type       ,  6) AS type
         , path
         , depth
     FROM  r_cte r
     WHERE NOT EXISTS
           (SELECT 0
             FROM  r_cte n
             WHERE n.id    = r.id
               AND n.depth > r.depth
           )
     ORDER BY
           depth
         , type
    ;
    ------------------------------------------------------------------------------
    
    ID     DEPENDENCY TYPE   PATH                                               DEPTH      
    ------ ---------- ------ -------------------------------------------------- -----------
        18                   18                                                           1
        55                   55                                                           1
        68                   68                                                           1
        78                   78                                                           1
        23 78                78,23                                                        2
        33 55                55,33                                                        2
        97 18                18,97                                                        2
        38 23                78,23,38                                                     3
         1 33         T_ALL  55,33,1                                                      3
         4 33         T_ALL  55,33,4                                                      3
        22 T_ALL      T_ALL  55,33,1,4,22                                                 4
    
      11 record(s) selected.
    This example works on my machines with DB2 9.1 on AIX64. Didn't knew the commands XMLSERIALIZE, thanks for that hint!

    Quote Originally Posted by tonkuma View Post
    I couldn't understand exactly.

    If the data was like the following, what result do you expect?
    Code:
    SELECT * FROM run;
    ------------------------------------------------------------------------------
    
    ID          DEPENDENCY TYPE 
    ----------- ---------- -----
              1 33         T_ALL
              4 33         T_ALL
              5 35         T_ALL
             33 55              
             35                 
             78                 
             23 78              
             38 23              
             22 T_ALL           
             68                 
             55                 
             18                 
             97 18              
            125 T_ALL           
    
      14 record(s) selected.
    I thought the result might be like the following.

    But, I have some questions.
    Q1) Where to put 35 for id=22 and 125?
    35,55,33,1,4,5,22
    55,35,33,1,4,5,22
    55,33,35,1,4,5,22
    55,33,1,4,35,5,22

    Q2) Is it neccesary to show type = T_ALL for id=22 and 125?

    Code:
    ID          DEPENDENCY TYPE  PATH                 DEPTH
    ----------- ---------- ----- -------------------- -----
             35                  35                   1
             78                  78                   1
             68                  68                   1
             55                  55                   1
             18                  18                   1
             33 55               55,33                2
             23 78               78,23                2
             97 18               18,97                2
              5 35         T_ALL 35,5                 2
             38 23               78,23,38             3
              1 33         T_ALL 55,33,1              3
              4 33         T_ALL 55,33,4              3
             22 T_ALL      T_ALL 55,33,1,4,5,22       4   -- where to put 35?
            125 T_ALL      T_ALL 55,33,1,4,5,125      4   -- where to put 35?
    Q1-A1: Because 35 has no dependency at all it should be before any other id, which has any dependency. So I would expect it at the beginning or after the 55 but before 33. Best would be, that 35 is the first in line, because id is smaller than 55, but that's only for the eyes...

    Q2-A2: No, it's not necessary, because they have the dependency T_ALL, they are not from the Type T_ALL.


    Quote Originally Posted by tonkuma View Post
    I thought another design of the table.

    If you put the list of preceding ids into dependency column,
    you would be able to keep more complex command job sequencies into the run table.

    And, it would be better to make the datatype of the dependency column as VARCHAR,
    because of ease of queries and save of disk space.

    For example:
    This data in the run table represents the following job networks.
    Code:
    ID     DEPENDENCY          
    ------ --------------------
         1 33                  
         4 33                  
         5 34                  
        33 55                  
        34                     
        78                     
        23 78                  
        38 23                  
        22 1,4,5               
        68                     
        55                     
        41                     
        43 41                  
        45 43,47               
        47 41                  
        49 47                  
       101                     
       141 47,101              
       125 1,4,5               
       201 125                 
       252 125,45              
       343 45,125,141          
       490 49,343
    Code:
         55                    68                    78        
         |                                           |
         |                                           |
         V                                           V
         33          34                              23
         ||          |                               |
         |+----+     |                               |
         |     |     |                               V
         V     V     V                               38
         01    04    05              41
         ||    ||    ||              ||
         ||    ||    |+----+         |+----+
         ||    |+----|----+|         |     |
         |+----|-----|---+||         V     V
         |     |     |   |||         43    47
         |+----+     |   |||         |     |||
         ||+---------+   |||         |+----+|+---------+
         |||             |||         ||     |          |
         VVV             VVV         VV     |     101  |
         22              125         45     |     |    |
                         |||         ||     |+----+    |
                   +-----+|+-----+   ||     ||         |
                   |      |      |   ||     VV         V
                   |      |+-----|---+|     141        49
                   |      ||     |+---+     |          |
                   |      ||     ||+--------+          |
                   |      ||     |||                   |
                   V      VV     VVV                   |
                   201    252    343                   |
                                 |                     |
                                 |+--------------------+
                                 ||
                                 VV
                                 490
    Yes, that was/is exactly my thought I also had and wanted to write, that one ID can have several Dependencys, which schould bei commaseperated like in your example!
    Yes, I see, varchar should be better, at first, I want to leave it as char. When it works as I want, I will refactor it, that's a good hint, thanks.

    I am trying to understand your example in the next posts you made. There you left out checking for T_ALL, or is my mind playing games with me this monday morning? :-) This is something I would need, in combination with the commaseperated Dependency list.

    I hope I cleared some Questions you had and didn't forget anything. I will read over it again today, is quite much at once monday morning ;-)

    I couldn't say it often enough: THANK YOU DB2-GODS!

  15. #15
    Join Date
    Aug 2011
    Posts
    25

    Talking

    ah sorry, you shortened it for ease just too early this morning....

Posting Permissions

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