6 Replies Latest reply: Jan 14, 2013 3:29 AM by 984097 RSS

    Need to find out the hierarchy information of a particular Menu

    984097
      I have written a query using "select ... start with initial-condition connect by recurse-condition" syntax to get the Hierarchical information for a prticular menu.

      select count(SUB_MENU_ID) from FND_MENU_ENTRIES_VL
      start with MENU_ID = 76580
      and SUB_MENU_ID is not null
      connect by prior menu_id=sub_menu_id

      O/P: *16*

      Now if I write the query to get the number of record manually upto level 2 thats gives me the output as *36*.

      select distinct(SUB_MENU_ID) from FND_MENU_ENTRIES_VL
      where MENU_ID = 76580
      and SUB_MENU_ID is not null
      union
      select distinct(SUB_MENU_ID) from FND_MENU_ENTRIES_VL where MENU_ID in(
      select distinct(SUB_MENU_ID) from FND_MENU_ENTRIES_VL where MENU_ID = 76580
      and SUB_MENU_ID is not null)

      Please let know whats wrong with the first query.
        • 1. Re: Need to find out the hierarchy information of a particular Menu
          sb92075
          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: Need to find out the hierarchy information of a particular Menu
            984097
            Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
            • 3. Re: Need to find out the hierarchy information of a particular Menu
              sb92075
              Realize that we don't have your tables & we don't have your data.
              How should we know what the "right" results should be when we don't understand the problem to be solved?

              How do I ask a question on the forums?
              SQL and PL/SQL FAQ
              • 4. Re: Need to find out the hierarchy information of a particular Menu
                984097
                SUB_MENU_ID     MENU_ID
                67723     72570
                67723     72581
                67829     72582
                67849     76580
                67850     67849
                68039     72878
                70604     72578
                70702     72578
                70734     76458
                71330     76580
                72570     76580
                72578     76580
                72581     76580
                72582     76580
                72712     76475
                72878     76580
                73144     73145
                73145     76580
                73244     76580
                74226     71330
                74356     76580
                76457     76458
                76458     76580
                76459     76580
                76460     76462
                76461     76462
                76462     76580
                76463     76466
                76464     76466
                76465     76466
                76466     76580
                76467     72578
                76475     76580
                76591     76466
                77139     76580
                77140     77139
                67849
                71330
                72578
                72582
                72878
                73244
                74356
                76458
                76459
                76462
                77139
                     76580
                • 5. Re: Need to find out the hierarchy information of a particular Menu
                  Frank Kulash
                  Hi,

                  Whenever you have a question, please post CREATE TABLE and INSERT statements for your sample data, so that the people who want to help you can re-create the problem and test their ideas. From looking at your posting:
                  981094 wrote:
                  ...
                  SUB_MENU_ID     MENU_ID
                  67723     72570
                  67723     72581
                  ...
                  77139
                       76580
                  It's impossible to know even which columns are NULL.
                  Read the forum FAQ {message:id=9360002} again, especially the part about sample data (section 7) and the part on \
                   tags (section 9).
                  
                  If you want to restrict a CONNECT BY query to LEVEL<x, then just include that condition in the CONNECT BY clause:
                  SELECT COUNT (sub_menu_id)
                  FROM      fnd_menu_entries_vl
                  START WITH     menu_id          = 76580
                  AND     sub_menu_id     IS NOT NULL
                  CONNECT BY      PRIOR menu_id     = sub_menu_id
                       AND     LEVEL           <= 2
                  ;
                  You could also put that condition in a WHERE clause, to discard the rows after they were found.  But it's more efficient to put conditions like that in the CONNECT BY clause, so the unwanted rows are not found in the first place.
                  
                  Your CONNECT BY query does not consider whether the sub_menu_ids found were distinct or not.  If you're trying to find an alternative query that does not use CONNECT BY, why are you including DISTINCT when that wasn't part of the original query?
                  
                  In the UNION query you posted, the first branch (before the keyword UNION) corresponds to LEVEL=1 of the CONNECT BY query, but the second branch (after the keyword UNION) corresponds to what would be LEVEL=2 if the CONNECT BY condition was 
                  "menu_id = PRIOR sub_menu_id", but for your CONNECT BY condition:
                  "PRIOR menu_id = sub_menu_id", a corresponding UNION query would be:
                  SELECT sub_menu_id
                  FROM      fnd_menu_entries_vl
                  WHERE     menu_id      = 76580
                  AND     sub_menu_id     IS NOT NULL
                  UNION ALL
                  SELECT sub_menu_id
                  FROM     fnd_menu_entries_vl
                  WHERE     sub_menu_id IN (
                            SELECT menu_id     -- DISTINCT not needed in an IN sub-query
                                 FROM fnd_menu_entries_vl
                                 WHERE menu_id     = 76580
                                 AND sub_menu_id     IS NOT NULL
                            )
                  ;
                  Depending on what results you want, you may or may not need SELECT *DISTINCT* outside of the IN-subquery, but you never need DISTINCT in the IN-subquery.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        
                  • 6. Re: Need to find out the hierarchy information of a particular Menu
                    984097
                    Thanks for the valuable information and suggestions.
                    It seems like CONNECT BY       menu_id     =PRIOR sub_menu_id solve my problem.
                    Thanks a lot.

                    Edited by: 981094 on Jan 14, 2013 1:29 AM

                    Edited by: 981094 on Jan 14, 2013 1:29 AM