1 Reply Latest reply: Dec 17, 2013 9:21 AM by CraigB RSS

    Hierarchy tree in oracle forms problem

    979801

      Hello Experts,

                           I am new in oracle forms.I am using oracle forms 11g with weblogic server 10.3.5 at windows 7.I have two table as tbl_country and tbl_state.I have to make a hierarchy tree in oracle forms.My table structure as:

      tbl_country

      CREATE TABLE "SCOTT"."TBL_COUNTRY" 
         (    "CNTRY_CODE" NUMBER NOT NULL ENABLE, 
          "NAME" VARCHAR2(80 BYTE), 
           CONSTRAINT "TBL_COUNTRY_PK" PRIMARY KEY ("CNTRY_CODE")   
         )
      

      tbl_state:

      CREATE TABLE "SCOTT"."TBL_STATE" 
         (    "SATE_CODE" NUMBER NOT NULL ENABLE, 
          "COUNTRY_CODE" NUMBER NOT NULL ENABLE, 
          "STATE_NM" VARCHAR2(80 BYTE), 
           CONSTRAINT "TBL_STATE_PK" PRIMARY KEY ("SATE_CODE")
           CONSTRAINT "FK_CNTRY_STATE" FOREIGN KEY ("COUNTRY_CODE")
         )
      

       

      Table Date as:

       insert into tbl_COUNTRY values(0,'country '); 
       insert into tbl_COUNTRY values(91,'HHH'); 
       insert into tbl_COUNTRY values(72,'III'); 
       insert into tbl_COUNTRY values(83,'JJJ');
      

       

      insert into tbl_state values(1,'state',0); 
       insert into tbl_state values(2,'BH',91); 
       insert into tbl_state values(3,'CI',72); 
       insert into tbl_state values(4,'DI',72); 
       insert into tbl_state values(5,'EH',91); 
       insert into tbl_state values(6,'FI',72); 
       insert into tbl_state values(7,'GJ',83); 
       insert into tbl_state values(8,'HJ',83); 
       insert into tbl_state values(9,'IH',91);
      

       

      Desired Output in oracle forms tree:

      |__Country

               |____HHH

                         |____BH

                         |____EH

                         |____IH

               |____III

                        |__CI

                        |__DI

                        |__FI

               |____JJJ

                         |__GJ

                         |__HJ

       

      I Have tried but got no output

      select1 ,level, esm.name,NULL,to_char(esm.CNTRY_CODE)
             from   (SELECT c.name,c.cntry_code from TBL_COUNTRY c union all select s.STATE_NM,s.COUNTRY_CODE from tbl_state s) esm
             connect by prior esm.CNTRY_CODE = esm.CNTRY_CODE
             start   with esm.code=0

      what is going wrong here.

      thank you

      regards

      aaditya