1 2 Previous Next 18 Replies Latest reply: Jan 10, 2013 6:51 AM by IamHariKrishna RSS

    help to write a join condition in oracle

    IamHariKrishna
      DB=10.2.0.5
      OS=RHEL 4
                
      SYS@MYDB> desc ENTERPRISEGROUPWAG
      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      WORKINGASSETGROUPINGID NOT NULL NUMBER(18)
      GROUPID NOT NULL NUMBER(9)
      ACTIVEDATE NOT NULL DATE
      INACTIVEDATE DATE
      LASTMODIFIED DATE
      BATCHID NUMBER(18)
      ENTERPRISEGROUPWAGID NUMBER
      VERSION_NUM NUMBER

      SYS@MYDB> desc ENTERPRISEGROUP
      Name Null? Type
      ----------------------------------------- -------- ----------------------------
      GROUPID NOT NULL NUMBER(9)
      ENTERPRISECD VARCHAR2(5)
      GROUPNAME VARCHAR2(30)
      PARENTGROUPID NUMBER(9)
      EXCLUSIVEGROUPNAME VARCHAR2(30)
      LASTMODIFIED DATE
      ACTIVEFLG NUMBER(9)
      VERSION_NUM NUMBER
      CREATEENDUSERID NUMBER(9)
      CREATEDATE DATE
      LASTMODIFIEDENDUSERID NUMBER(9)

      I need the records which are in ENTERPRISEGROUPWAG NOT IN ENTERPRISEGROUP table.

      SQL> select count(distinct groupid) from ENTERPRISEGROUPWAG
      where groupid not in (select groupid from ENTERPRISEGROUP where activeflg = 1) and inactivedate is null; 2

      COUNT(DISTINCTGROUPID)
      ----------------------
      1064

      Elapsed: 00:00:00.15

      I need these records from both tables. I wrote below query but I suspect its not giving exact output (same count as above)


      select e.GROUPID,e.GROUPNAME,e.PARENTGROUPID,ew.WORKINGASSETGROUPINGID
                from ENTERPRISEGROUP e, ENTERPRISEGROUPWAG ew
                WHERE ew.INACTIVEDATE is not null
                ew.GROUPID NOT IN (select GROUPID from ENTERPRISEGROUP where activeflg = 1)
                AND ew.INACTIVEDATE is not null
                /

      --its returning more than '1064' records...Can anybody help me in writing the sql query to find those records pls.

      Thanks
        1 2 Previous Next