Home » SQL & PL/SQL » SQL & PL/SQL » Filter in hierarchical query
Filter in hierarchical query [message #286773] Mon, 10 December 2007 01:50 Go to next message
pragatimathur
Messages: 42
Registered: July 2006
Location: DELHI
Member
Please check the attachment
Re: Filter in hierarchical query [message #286775 is a reply to message #286773] Mon, 10 December 2007 01:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Pleas post in text file, doc files are not downloaded by many of us.
In addition, I don't know what's inside the file but are you sure that:
- the problem is well explained
- there is the full version (4 decimals)
- there is a test case (create table and insert statements)
- there is the result for this test case
- the whole is well formatted

Regards
Michel

[Updated on: Mon, 10 December 2007 01:55]

Report message to a moderator

Re: Filter in hierarchical query [message #286786 is a reply to message #286775] Mon, 10 December 2007 02:18 Go to previous messageGo to next message
pragatimathur
Messages: 42
Registered: July 2006
Location: DELHI
Member
My sql query and output

Select Level As Level_Id,
 Menu_Id,
Program_Id,
Seq_Id,
Case When Level = 2 Then '       '||Descr
                                 When Level = 3 Then'              '||Descr
          When Level = 4 Then'                 '||Descr
 Else Descr
End Descr,
Module_Nm
From(
Select
Menu_Id,
Program_Id,
Seq_Id,
Descr,
Module_Nm
From(
Select  Menu_Id,
Program_Id,
Seq_Id,
Module_Nm,
Descr
From(
Select
Menu_Tree.Seq_Id ,
V_Menu_Tree.Menu_Id ,
V_Menu_Tree.Program_Id,
V_Menu_Tree.Module_Nm,
V_Menu_Tree.Descr
From
Sc_Mnu_Tree V_Menu_Tree ,
Sc_Role_Priv_Templ
Where   V_Menu_Tree.Menu_Id  = Sc_Role_Priv_Templ.Menu_Id
And   V_Menu_Tree.Program_Id  = Sc_Role_Priv_Templ.Program_Id
And  Sc_Role_Priv_Templ.Role_Cd= 'Admin'
And V_Menu_Tree.Module_Nm ='CA'
Order By Module_Nm,Seq_Id
)))
Start With Program_Id='Null'
Connect By
Prior Menu_Id = Program_Id
Order Siblings By Seq_Id


OUTPUT-

  LEVEL_ID MENU_ID         PROGRAM_ID SEQ_ID      DESCR                                                       
---------- ----------      ---------- ----------  --------------------------------------------------------
         1	CA         NULL                2  Cross Application                                           
         2 	CA_GEN     CA                  1  General                                              
         3 	CA_TAC     CA_GEN              1  Terms and Conditions                          
         3 	CA_COUNTRY CA_GEN              2  Country                                       
         3 	CA_STATE   CA_GEN              3  State                                         
         3 	CA_INPO    CA_GEN              4  Inland point                                  
         3 	CA_PORT    CA_GEN              5  Port                                          
         3 	CA_SALMAN  CA_GEN              6  Salesman                                      
         3 	CA_COMDTY  CA_GEN              7  Commodity                                     
         3 	CA_COMGRP  CA_GEN              8  Commodity Group                               
         3 	CA_CONCD   CA_GEN              9  Consortium Code                               


My query is
I want filter in Menu_id
If Menu_id =’CA_TAC’ Then
Output should be this
LEVEL_ID    MENU_ID     PROGRAM_ID     SEQ_ID DESCR                                                       
----------  ----------  ---------- ---------- --------------------------------------------------------
         1  CA          NULL                2 Cross Application                                           
         2  CA_GEN      CA                  1 General                                              
         3  CA_TAC      CA_GEN              1 Terms and Conditions 


With no other menu_id.

but i am not able to filter it. so please help me out

[Mod-edit: Frank added code-tags and lined-up output]

[Updated on: Mon, 10 December 2007 02:37] by Moderator

Report message to a moderator

Re: Filter in hierarchical query [message #286793 is a reply to message #286775] Mon, 10 December 2007 02:35 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 10 December 2007 08:52

In addition, I don't know what's inside the file but are you sure that:
- the problem is well explained
- there is the full version (4 decimals)
- there is a test case (create table and insert statements)
- there is the result for this test case
- the whole is well formatted


Regards
Michel

Re: Filter in hierarchical query [message #286804 is a reply to message #286793] Mon, 10 December 2007 02:54 Go to previous messageGo to next message
pragatimathur
Messages: 42
Registered: July 2006
Location: DELHI
Member
I just want to know that how to filter the leaf node in
Hierarchical query so that hierarchy of that leaf node comes
which i am passing in parameter.

Please help me out....
Re: Filter in hierarchical query [message #286812 is a reply to message #286804] Mon, 10 December 2007 03:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just want you to post what I requested.
Please post it.

Regards
Michel
Re: Filter in hierarchical query [message #286824 is a reply to message #286812] Mon, 10 December 2007 03:17 Go to previous messageGo to next message
pragatimathur
Messages: 42
Registered: July 2006
Location: DELHI
Member
CREATE TABLE SC_MNU_TREE (
SYSLVL1 VARCHAR2 (1) NOT NULL,
SYSLVL2 VARCHAR2 (1) NOT NULL,
SYSLVL3 VARCHAR2 (3) NOT NULL,
MENU_ID VARCHAR2 (10) NOT NULL,
PROGRAM_ID VARCHAR2 (10) NOT NULL,
SEQ_ID NUMBER (10),
DESCR VARCHAR2 (70),
WIN_NM VARCHAR2 (45),
LIB_NM VARCHAR2 (40),
MODULE_NM VARCHAR2 (40),
MENU_TYPE VARCHAR2 (1) DEFAULT 'F',
PROG_SOURCE CHAR (1),
IMAGE_NM VARCHAR2 (40),
URL_NM VARCHAR2 (255),
MENU_ITEM_VISIBLE VARCHAR2 (1),
CONSTRAINT PK_SC_MNU_TREE
PRIMARY KEY ( MENU_ID, PROGRAM_ID ) ) ;

INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_FNACC', 'CA_FN', 8, 'Account Levels', 'w_mnt_acc_lvl1', NULL
, 'CA', 'F', NULL, NULL, 'ca/account_levels.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_COMGRP', 'CA_GEN', 8, 'Commodity Group', NULL, NULL, 'CA', 'F'
, 'A', NULL, 'ca/cacommoditygroup.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_DEPOT', 'CA_EQ', 4, 'Depot Master', 'pw_mnt_Depot', NULL, 'CA'
, 'F', 'A', NULL, 'ca/cadepotmasterlist.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_BKCODE', 'CA_FN', 2, 'Bank Code', 'pw_mnt_bank', NULL, 'CA', 'F'
, 'A', NULL, 'ca/bankcodelist.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_SL_CAT', 'CA_FN', 3, 'Sub Ledger Category', 'w_mnt_sub_ledger_category'
, NULL, 'CA', 'F', 'A', NULL, 'CA/sub-ledgercategorylist.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_TAXCODE', 'CA_FN', 6, 'Tax Code', 'pw_mnt_tax_code', NULL, 'CA'
, 'F', 'A', NULL, 'ca/taxcodelist.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_COUNTRY', 'CA_GEN', 2, 'Country', 'w_mnt_country', NULL, 'CA'
, 'F', 'A', NULL, 'ca/countrysrch.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_STATE', 'CA_GEN', 3, 'State', 'w_mnt_state_code', NULL, 'CA'
, 'F', 'A', NULL, 'ca/state.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_GEN', 'CA', 1, 'General', NULL, NULL, 'CA', 'F', 'A', NULL, NULL
, 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_EQ', 'CA', 2, 'Equipment', NULL, NULL, 'CA', 'F', 'A', NULL, NULL
, 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_FN', 'CA', 3, 'Finance', NULL, NULL, 'CA', 'F', 'A', NULL, NULL
, 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_CHGCODE', 'CA_FN', 4, 'Charge Code', 'pw_mnt_charge_code', NULL
, 'CA', 'F', 'A', NULL, 'ca/chargecodelist.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_CHGGRP', 'CA_FN', 5, 'Charge Group', 'pw_mnt_charge_category_group'
, NULL, 'CA', 'F', 'A', NULL, 'ca/chargegroup.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_EQCATG', 'CA_EQ', 1, 'Equipment Category', 'pw_mnt_equip_category'
, NULL, 'CA', NULL, 'A', NULL, 'ca/equcatg.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_EQTYPE', 'CA_EQ', 2, 'Equipment Type', 'w_mnt_equip_type', NULL
, 'CA', NULL, 'A', NULL, 'ca/equipmenttypelist.jsp', 'Y');
INSERT INTO SC_MNU_TREE ( SYSLVL1, SYSLVL2, SYSLVL3, MENU_ID, PROGRAM_ID, SEQ_ID, DESCR, WIN_NM,
LIB_NM, MODULE_NM, MENU_TYPE, PROG_SOURCE, IMAGE_NM, URL_NM, MENU_ITEM_VISIBLE ) VALUES (
'E', '*', '***', 'CA_TERM', 'CA_EQ', 3, 'Terminal', 'w_mnt_terminal', NULL, 'CA', 'F'
, 'A', NULL, 'ca/terminalsrch.jsp', 'Y');
commit;

Thank You
Re: Filter in hierarchical query [message #286827 is a reply to message #286824] Mon, 10 December 2007 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Mon, 10 December 2007 09:35

Michel Cadot wrote on Mon, 10 December 2007 08:52

In addition, I don't know what's inside the file but are you sure that:
- the problem is well explained
- there is the full version (4 decimals)
- there is a test case (create table and insert statements)
- there is the result for this test case
- the whole is well formatted



OraFAQ Forum Guide, "How to format your post?" section.

Regards
Michel
Re: Filter in hierarchical query [message #286871 is a reply to message #286827] Mon, 10 December 2007 05:36 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Start With menu_id = 'CA_TAC'
Connect By
Menu_Id = PRIOR Program_Id
Order Siblings By Seq_Id


Ross Leishman
Re: Filter in hierarchical query [message #287176 is a reply to message #286871] Tue, 11 December 2007 05:51 Go to previous message
pragatimathur
Messages: 42
Registered: July 2006
Location: DELHI
Member
Thanks A Lot
But hierarchy was coming in the reverse form
so i have used,

START WITH menu_ID='CA_TAC'
CONNECT BY PRIOR program_id= menu_id
ORDER BY ROWNUM DESC

Thank You

Previous Topic: Avarege over 5 minutes
Next Topic: regarding count
Goto Forum:
  


Current Time: Sat Dec 03 08:11:00 CST 2016

Total time taken to generate the page: 0.04615 seconds