1 2 Previous Next 18 Replies Latest reply: Apr 27, 2011 5:00 AM by munky RSS

    Problem with hierarchical queries

    646913
      Hi,

      I implemented (thx to jarola) sucessfully a report using a hierarchical view.

      It is nearly the same as in Tyler Muths blog: -http://tylermuth.wordpress.com/2009/02/26/hierarchical-query-to-unordered-list/-

      -select (level*20)-20 the_level ,(level*10)-10 the_level2, machine_no
      - parent_id, item_id,description
      -from machine_list
      -where machine_no = 101
      -and project_id = 122
      - connect by prior item_id = parent_id
      - start with parent_id is null


      This works good, as long as there is just one project/machine in the table.
      If I have 2 machines in the table, it does not work in the report anymore, all entries are shown, although I check for the right machine_number and project_number in the query :-(

      Any suggestions?

      BR
      Lena
        • 1. Re: Problem with hierarchical queries
          munky
          Hi

          Firstly, please can you format your code when posting on the forum with {noformat}
          {noformat} tags so that it's a little more readable.
          
          Secondly, please can you post CREATE TABLE and INSERT statements so that we can replicate your data. Have you tried running this in SQLplus or SQL Developer to see what the results of the actual query are?
          
          Cheers
          
          Ben                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
          • 2. Re: Problem with hierarchical queries
            646913
            Hi,

            thanks for the hint with
            .
            
            Regarding the data: when I just select the lines in toad without 'connect by prior... start with...' it works fine :-(
            If I do use connect etc, I get multiple lines. The table and the insert statements are unfortunately a bit too bulky to post :-(
            
            
            Update/Copying: I just insert the copy of the first record set with another machine number.
            So basically they are the same 96 lines except the machine_no.
            
            BR
            Lena
            
            Edited by: Lena F on Apr 26, 2011 3:33 AM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
            • 3. Re: Problem with hierarchical queries
              munky
              >
              If I do use connect etc, I get multiple lines. The table and the insert statements are unfortunately a bit too bulky to post
              >
              Just need enough data to replicate the results that you are getting, it's too difficult to troubleshoot with hypotheticals...

              You should be able to recreate the replication with 5-10 INSERTS
              • 4. Re: Problem with hierarchical queries
                646913
                Can I mail it too?

                Might be easier than to post.

                BR
                Lena
                • 5. Re: Problem with hierarchical queries
                  munky
                  Sure, but I won't get it until this evening...

                  Cheers

                  Ben
                  • 6. Re: Problem with hierarchical queries
                    646913
                    it's on the way...

                    BR & thank you,
                    Lena
                    • 7. Re: Problem with hierarchical queries
                      Joel_C
                      I hope this isn't a trivial suggestion but, have you ensured that the items in your table are correctly aligned to a relevant parent_id? The way you've written the query, every row with a blank parent ID will be returned as the root of it's own tree - therefore if every parent id is blank, every row will be returned. This would be consistent with every row being returned if you remove the references to "connect by prior...start with" etc,etc.
                      • 8. Re: Problem with hierarchical queries
                        646913
                        Hi Joel,

                        the alignment of parent ids is correct - there are just a few root items (e.g. 10 of 100) with an empty parent_id.

                        Thank you,
                        BR
                        Lena
                        • 9. Re: Problem with hierarchical queries
                          TexasApexDeveloper
                          Can you post the data via inserts and a structure in this thread and OTHERS maybe able to help further??

                          Thank you,

                          Tony Miller
                          Webster, TX

                          While it is true that technology waits for no man; stupidity will always stop to take on new passengers.

                          If this question is answered, please mark the thread as closed and assign points where earned..
                          • 10. Re: Problem with hierarchical queries
                            646913
                            Hi,

                            the create table statement would be like that:

                            {     CREATE TABLE machine_list
                            (
                            MACHINE_NO NUMBER(8),
                            ITEM_ID NUMBER(8),
                            PARENT_ID NUMBER(8),
                            SEQ_ID NUMBER(8),
                            DESCR VARCHAR2(100 CHAR))}

                            a few insert statements would be like follows:
                            {Insert into MACHINE_LIST
                               (MACHINE_NO, ITEM_ID, SEQ_ID, DESCR)
                            Values
                               (101, 101, 1, 'Inspection');
                            Insert into MACHINE_LIST
                               (MACHINE_NO, ITEM_ID, SEQ_ID, DESCR)
                            Values
                               (101, 102, 2, 'Rebuild Casing');
                            Insert into MACHINE_LIST
                               (MACHINE_NO, ITEM_ID, PARENT_ID, SEQ_ID, DESCR)
                            Values
                               (101, 103, 102, 3, 'Standard Repair 58/60');
                            Insert into MACHINE_LIST
                               (MACHINE_NO, ITEM_ID, PARENT_ID, SEQ_ID, DESCR)
                            Values
                               (101, 104, 103, 4, 'Spares');
                            Insert into MACHINE_LIST
                               (MACHINE_NO, ITEM_ID, SEQ_ID, DESCR)
                            Values
                               (102, 101, 1, 'Inspection');
                            Insert into MACHINE_LIST
                               (MACHINE_NO, ITEM_ID, SEQ_ID, DESCR)
                            Values
                               (102, 102, 2, 'Rebuild Casing');
                            Insert into MACHINE_LIST
                               (MACHINE_NO, ITEM_ID, PARENT_ID, SEQ_ID, DESCR)
                            Values
                               (102, 103, 102, 3, 'Standard Repair 58/60');
                            Insert into MACHINE_LIST
                               (MACHINE_NO, ITEM_ID, PARENT_ID, SEQ_ID, DESCR)
                            Values
                               (102, 104, 103, 4, 'Spares');
                            }

                            BR
                            Lena
                            • 11. Re: Problem with hierarchical queries
                              Joel_C
                              Sorry, just as a wee thing, I think you've misinterpreted what was meant by the code tag. When you write "{ code}", it literally means "{ code}", not "{put-your-code-here}" - here's your code reformatted accordingly:
                               CREATE TABLE machine_list
                              (
                              MACHINE_NO NUMBER(8),
                              ITEM_ID NUMBER(8),
                              PARENT_ID NUMBER(8),
                              SEQ_ID NUMBER(8),
                              DESCR VARCHAR2(100 CHAR))
                              a few insert statements would be like follows:
                              Insert into MACHINE_LIST
                              (MACHINE_NO, ITEM_ID, SEQ_ID, DESCR)
                              Values
                              (101, 101, 1, 'Inspection');
                              Insert into MACHINE_LIST
                              (MACHINE_NO, ITEM_ID, SEQ_ID, DESCR)
                              Values
                              (101, 102, 2, 'Rebuild Casing');
                              Insert into MACHINE_LIST
                              (MACHINE_NO, ITEM_ID, PARENT_ID, SEQ_ID, DESCR)
                              Values
                              (101, 103, 102, 3, 'Standard Repair 58/60');
                              Insert into MACHINE_LIST
                              (MACHINE_NO, ITEM_ID, PARENT_ID, SEQ_ID, DESCR)
                              Values
                              (101, 104, 103, 4, 'Spares');
                              Insert into MACHINE_LIST
                              (MACHINE_NO, ITEM_ID, SEQ_ID, DESCR)
                              Values
                              (102, 101, 1, 'Inspection');
                              Insert into MACHINE_LIST
                              (MACHINE_NO, ITEM_ID, SEQ_ID, DESCR)
                              Values
                              (102, 102, 2, 'Rebuild Casing');
                              Insert into MACHINE_LIST
                              (MACHINE_NO, ITEM_ID, PARENT_ID, SEQ_ID, DESCR)
                              Values
                              (102, 103, 102, 3, 'Standard Repair 58/60');
                              Insert into MACHINE_LIST
                              (MACHINE_NO, ITEM_ID, PARENT_ID, SEQ_ID, DESCR)
                              Values
                              (102, 104, 103, 4, 'Spares');
                              • 12. Re: Problem with hierarchical queries
                                munky
                                So here's the example for code tags (sorry I didn't get a chance to reply to the OP last night - It was a nice sunny day over here and we went for a few swift halfs after work!)

                                When you post you would write this...

                                {noformat}
                                {noformat}
                                SELECT * 
                                FROM emp;
                                {noformat}
                                {noformat}

                                This would then display like this...
                                SELECT *
                                FROM emp;
                                Cheers

                                Ben
                                • 13. Re: Problem with hierarchical queries
                                  munky
                                  Hi Joel

                                  Just for reference, to write the code tags in posts without a space, you can use noformat tags on either side like this (let's see if I can manage this!)...

                                  <pre>{noformat }
                                  {noformat }</pre>
                                  
                                  Edited by: Munky on Apr 27, 2011 10:03 AM
                                  
                                  Edited by: Munky on Apr 27, 2011 10:03 AM
                                  
                                  Edited by: Munky on Apr 27, 2011 10:03 AM
                                  
                                  Edited by: Munky on Apr 27, 2011 10:04 AM
                                  
                                  Edited by: Munky on Apr 27, 2011 10:04 AM
                                  
                                  Edited by: Munky on Apr 27, 2011 10:05 AM - sod it! had to add spaces to the noformat tags!                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                                  • 14. Re: Problem with hierarchical queries
                                    646913
                                    Hi,

                                    thanks for all the suggestions - we managed to solve the problem.

                                    The restrictions regarding the data selection have to be made within the start with statement and not in the where-clause.
                                    we also added the proj_id to the connect-statement.

                                    That did solve the problem!

                                    BR
                                    Lena
                                    1 2 Previous Next