1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to display value based on connect by return value

Discussion in 'SQL PL/SQL' started by chalie003, Nov 26, 2014.

  1. chalie003

    chalie003 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    55
    hi i what to do the following
    use connect by level and keep level = 2 and

    3. keep sys_connect_by_path indicator should be null and

    4. then replace its value with either the parent or the child depends how i do the traversing and

    5. then generate the data...

    but my query is returning null


    hi how can i display the real name of both parentname and childname


    Code (SQL):
    CREATE TABLE CAL_ERD
    (
      cal_erdid    NUMBER(5) NOT NULL,
      obj_child    NUMBER(5) NOT NULL,
      obj_parent   NUMBER(5) NOT NULL,
      obj_rel_type VARCHAR2(50)
    );



    CREATE TABLE CAL_OBJ
    (
      cal_objid   NUMBER(4) NOT NULL,
      obj_name    VARCHAR2(100),
      obj_type    VARCHAR2(50),
      obj_title   VARCHAR2(50),
      description VARCHAR2(50)
    );

    ALTER TABLE CAL_ERD
      ADD CONSTRAINT CAL_OBJ_CHILD_FK FOREIGN KEY (OBJ_CHILD)
      REFERENCES CAL_OBJ (CAL_OBJID);
    ALTER TABLE CAL_ERD
      ADD CONSTRAINT CAL_OBJ_PARENT_FK FOREIGN KEY (OBJ_PARENT)
      REFERENCES CAL_OBJ (CAL_OBJID);
     
      ALTER TABLE CAL_ERD
      ADD CONSTRAINT CAL_ERD_PK PRIMARY KEY (CAL_ERDID, OBJ_CHILD, OBJ_PARENT);



      INSERT INTO cal_erd(obj_child,
                          obj_parent)
                       VALUES(1192,
                              1193);
                             
          INSERT INTO cal_erd(obj_child,
                          obj_parent)
                       VALUES(1194,
                              1193);
                             
                             
                             
               

    -- Create sequence
    CREATE SEQUENCE CAL_ERD_SEQ
    minvalue 1
    maxvalue 999999999999999999999999999
    START WITH 1592
    INCREMENT BY 1
    cache 20;

    -- Create sequence
    CREATE SEQUENCE CAL_OBJ_SEQ
    minvalue 1
    maxvalue 999999999999999999999999999
    START WITH 1212
    INCREMENT BY 1
    cache 20;


    CREATE OR REPLACE TRIGGER insert_Cal_seq
      BEFORE INSERT ON cal_ERD
      FOR EACH ROW
    BEGIN
        SELECT cal_ERD_SEQ.NEXTVAL INTO :NEW.cal_erdid FROM dual;
    END;

    CREATE OR REPLACE TRIGGER insert_seq
      BEFORE INSERT ON cal_obj
      FOR EACH ROW
    BEGIN
        SELECT cal_obj_seq.NEXTVAL INTO :NEW.cal_objId FROM dual;
    END;


    INSERT INTO cal_obj(obj_name,
                        obj_type,
                        obj_title)
                     VALUES('MainObject',
                            'FORM',
                            'Fmb')
                           
                             INSERT INTO cal_obj(obj_name,
                        obj_type,
                        obj_title)
                     VALUES('SecondObject',
                            'FORM',
                            'Fmb')
                           
                              INSERT INTO cal_obj(obj_name,
                        obj_type,
                        obj_title)
                     VALUES('ThirdObject',
                            'FORM',
                            'Fmb')         
                             
                             
                             
                                SELECT OJ.OBJ_NAME,ER.OBJ_CHILD,ER.OBJ_PARENT,
             sys_connect_by_path(oj.obj_name,'/')path,
             connect_by_root oj.obj_name
             FROM cal_obj oj, cal_erd er
             WHERE oj.cal_objid = er.obj_child
             START WITH er.obj_parent IS NULL
             CONNECT BY prior er.obj_child = er.obj_parent;