5 Replies Latest reply: Jul 30, 2013 5:32 AM by 969483 RSS

    Connect By Query

    969483

      Hi,

       

      Here is some sample data:

       

      with dummy_data AS
          (   SELECT 35505 projectID, 3789407 taskID, '' parent_task_id, 'A' task_number FROM DUAL UNION ALL
      SELECT 35505 projectID, 3789408 taskID, '3789407' parent_task_id, 'A01' task_number FROM DUAL UNION ALL
      SELECT 35505 projectID, 3789409 taskID, '3789407' parent_task_id, 'A07' task_number FROM DUAL UNION ALL
      SELECT 35505 projectID, 3789410 taskID, '3789407' parent_task_id, 'A05' task_number FROM DUAL UNION ALL
      SELECT 35505 projectID, 3789411 taskID, '3789407' parent_task_id, 'A10' task_number FROM DUAL UNION ALL
      SELECT 35505 projectID, 3789412 taskID, '3789407' parent_task_id, 'A12' task_number FROM DUAL UNION ALL
      SELECT 35505 projectID, 3789413 taskID, '3789407' parent_task_id, 'A14' task_number FROM DUAL UNION ALL
      SELECT 35505 projectID, 3789414 taskID, '3789407' parent_task_id, 'A15' task_number FROM DUAL UNION ALL
      SELECT 35505 projectID, 3789415 taskID, '3789408' parent_task_id, 'A0101' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346398 taskID, '' parent_task_id, 'A' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346399 taskID, '5346398' parent_task_id, 'A01' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346400 taskID, '5346399' parent_task_id, 'A0101' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346402 taskID, '5346398' parent_task_id, 'A05' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346403 taskID, '5346398' parent_task_id, 'A06' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346404 taskID, '5346398' parent_task_id, 'A10' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346405 taskID, '5346398' parent_task_id, 'A12' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346406 taskID, '5346405' parent_task_id, 'A1204' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346407 taskID, '5346398' parent_task_id, 'A14' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346408 taskID, '5346407' parent_task_id, 'A1401' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346410 taskID, '5346398' parent_task_id, 'A15' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346411 taskID, '5346410' parent_task_id, 'A1501' task_number FROM DUAL UNION ALL
      SELECT 63486 projectID, 5346413 taskID, '5346398' parent_task_id, 'A16' task_number FROM DUAL)
                  SELECT LPAD (' '
                     , (LEVEL - 1) * 10
                     , ' ')
                 || task_number my_label
               , LEVEL
               , taskID
               , task_number
               , parent_task_id
            FROM dummy_data
      START WITH taskID = 5346398
      CONNECT BY PRIOR taskID = parent_task_id;
      
      

       

      Output (sort of - it gets a bit messed up with this editor)

      MY_LABEL          LEVEL          TASK_ID          TASK_NUMBER          PARENT_TASK_ID
      A               1          5346398          A     
         A01               2          5346399          A01               5346398
            A0101          3          5346400          A0101               5346399
         A05               2          5346402          A05               5346398
         A06               2          5346403          A06               5346398
         A10               2          5346404          A10               5346398
         A12               2          5346405          A12               5346398
            A1204          3          5346406          A1204               5346405
         A14               2          5346407          A14               5346398
            A1401          3          5346408          A1401               346407
         A15               2          5346410          A15               5346398
            A1501          3          5346411          A1501               5346410
         A16               2          5346413          A16               5346398
      

       

      What I need to be able to do is to only return those rows on level 2 which also have a sub-task at level 3.

       

      I tried:

       

                  SELECT LPAD (' '
                     , (LEVEL - 1) * 10
                     , ' ')
                 || task_number my_label
               , LEVEL
               , taskID
               , task_number
               , parent_task_id
            FROM dummy_data WHERE LEVEL IN (2,3)
      
      START WITH taskID = 5346398
      CONNECT BY PRIOR taskID = parent_task_id;
      


      But that also returns lines which are not parent tasks...


      I wondered if I could ask for some advice about how to achieve this please?


      Any advice much appreciated.


      Thanks!

        • 1. Re: Connect By Query
          Frank Kulash

          Hi,

           

          Thanks for posting the sample data.  Don't forget to post the exact results you want from that data.

          It's nice to show the current results, too (and I agree, this site is very annoying about how it formats things), but as long as you post the sample data and the query, anoyone can re-run the query and produce the current output.  You're the one who knows for sure what the desired output is, so you really need to post that.

           

          It sounds like you need a WHERE clause, like the one below:

           

                      SELECT LPAD (' '

                         , (LEVEL - 1) * 10  -- See note below

                         , ' ')

                     || task_number my_label

                   , LEVEL

                   , taskID

                   , task_number

                   , parent_task_id

                FROM dummy_data

          WHERE    LEVEL = 2

          AND    CONNECT_BY_ISLEAF = 0

          START WITH taskID = 5346398

          CONNECT BY PRIOR taskID = parent_task_id;

           

          Here's the output I got from your sample data:

           

          MY_LABEL           LEVEL     TASKID TASK_ PARENT_

          ------------- ---------- ---------- ----- -------

                    A01          2    5346399 A01   5346398

                    A12          2    5346405 A12   5346398

                    A14          2    5346407 A14   5346398

                    A15          2    5346410 A15   5346398


          NOTE: Since all of the output is coming from the same LEVEL, do you really need to indent by LEVEL?

          • 2. Re: Connect By Query
            Solomon Yakobson

            with dummy_data as (

                                SELECT 35505 projectID, 3789407 taskID, '' parent_task_id, 'A' task_number FROM DUAL UNION ALL 

                                SELECT 35505 projectID, 3789408 taskID, '3789407' parent_task_id, 'A01' task_number FROM DUAL UNION ALL 

                                SELECT 35505 projectID, 3789409 taskID, '3789407' parent_task_id, 'A07' task_number FROM DUAL UNION ALL 

                                SELECT 35505 projectID, 3789410 taskID, '3789407' parent_task_id, 'A05' task_number FROM DUAL UNION ALL 

                                SELECT 35505 projectID, 3789411 taskID, '3789407' parent_task_id, 'A10' task_number FROM DUAL UNION ALL 

                                SELECT 35505 projectID, 3789412 taskID, '3789407' parent_task_id, 'A12' task_number FROM DUAL UNION ALL 

                                SELECT 35505 projectID, 3789413 taskID, '3789407' parent_task_id, 'A14' task_number FROM DUAL UNION ALL 

                                SELECT 35505 projectID, 3789414 taskID, '3789407' parent_task_id, 'A15' task_number FROM DUAL UNION ALL 

                                SELECT 35505 projectID, 3789415 taskID, '3789408' parent_task_id, 'A0101' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346398 taskID, '' parent_task_id, 'A' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346399 taskID, '5346398' parent_task_id, 'A01' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346400 taskID, '5346399' parent_task_id, 'A0101' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346402 taskID, '5346398' parent_task_id, 'A05' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346403 taskID, '5346398' parent_task_id, 'A06' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346404 taskID, '5346398' parent_task_id, 'A10' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346405 taskID, '5346398' parent_task_id, 'A12' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346406 taskID, '5346405' parent_task_id, 'A1204' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346407 taskID, '5346398' parent_task_id, 'A14' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346408 taskID, '5346407' parent_task_id, 'A1401' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346410 taskID, '5346398' parent_task_id, 'A15' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346411 taskID, '5346410' parent_task_id, 'A1501' task_number FROM DUAL UNION ALL 

                                SELECT 63486 projectID, 5346413 taskID, '5346398' parent_task_id, 'A16' task_number FROM DUAL

                               )  

            SELECT  LPAD (' ',(LEVEL - 1) * 10) || task_number my_label,  

                    LEVEL,

                    taskID,

                    task_number,

                    parent_task_id

              FROM  dummy_data

              WHERE LEVEL = 2

                AND CONNECT_BY_ISLEAF = 0

              START WITH taskID = 5346398

              CONNECT BY PRIOR taskID = parent_task_id

                     AND LEVEL <= 3

            /


            MY_LABEL                            LEVEL     TASKID TASK_ PARENT_
            ------------------------------ ---------- ---------- ----- -------
                      A01                           2    5346399 A01   5346398
                      A12                           2    5346405 A12   5346398
                      A14                           2    5346407 A14   5346398
                      A15                           2    5346410 A15   5346398

            SQL>

             

            SY.

            • 3. Re: Connect By Query
              969483

              Hi Frank,

               

              Thanks for your reply.

               

              I was hoping to also get the relevant level 3 line as well - e.g. the line 2 which you have kindly found a way to ID, but I wondered if it is possible to get the sub-level 3 for them as well?

               

              MY_LABELLEVELTASK_IDTASK_PARENT_
                    A01
              2534639953463995346398
                              A0101
              35346400A01015346399
                    A12
              25346405A125346398
                              A1204
              35346406A12045346405
                    A14
              25346407A145346398
                              A1401
              35346408A14015346407
                    A15
              25346410A155346398
                              A1501
              35346411A15015346410

               

              Thanks again...

              • 4. Re: Connect By Query
                Frank Kulash

                Hi,

                 

                969483 wrote:

                 

                Hi Frank,

                 

                Thanks for your reply.

                 

                I was hoping to also get the relevant level 3 line as well - e.g. the line 2 which you have kindly found a way to ID, but I wondered if it is possible to get the sub-level 3 for them as well? ...

                Is that what you asked for, or did you ask for level 2 only?

                969483 wrote:

                 

                ... What I need to be able to do is to only return those rows on level 2 which also have a sub-task at level 3....

                 

                 

                Always post the exact results you want from the given data.

                 

                To include level 3, add "OR LEVEL = 3 to the WHERE clause.  When using both AND and OR in the same condition, use parentheses to make the meaning unambiguous:

                 

                         SELECT LPAD (' '

                                     , (LEVEL - 1) * 10  -- See note below

                                     , ' ')

                                            || task_number my_label

                         ,      LEVEL

                         ,      taskID

                         ,      task_number

                         ,      parent_task_id

                         FROM   dummy_data

                WHERE    (      LEVEL             = 2

                         AND    CONNECT_BY_ISLEAF = 0

                         )

                OR       LEVEL   = 3

                START WITH  taskID = 5346398

                CONNECT BY  PRIOR taskID = parent_task_id;

                • 5. Re: Connect By Query
                  969483

                  Sorry Frank, you are right, looking back I asked to only return Level 2 which were parent tasks.

                   

                  Thanks very much for your helpful reply. I will be more precise next time I ask a question here.

                   

                  Thanks again, much appreciated.