3 Replies Latest reply: Sep 8, 2012 11:45 AM by whiteline _ RSS

    Intersection of data

      Hello Everyone.

      I need help creating data table.

       

      Please see sample data below.

       

      refdata:
      LOAD * INLINE
      [level0,level1,level2,level3,level4, sid,locid
      tot market, Ra/ Tre / Fin,    Ra Treas Fin,Ra Trading, Ra NA,1,11
      tot market, Ra/ Tre / Fin,    Ra Treas Fin,Ra Trading, Ra NA,3,22
      tot market, Ra/ Tre / Fin,    Ra Treas Fin, Fin Dk,,5,22
      ];
      
      SidData:
      LOAD sid as sidTemp,
           gid as gidTemp1;
      LOAD * INLINE
      [sid, gid
      1,12
      1,123
      3,23
      3,24
      5,66
      5,77
      5,75
      ];
      
      
      LocData:
      LOAD locid as locidTemp,
           gid as gidTemp2;
      LOAD * INLINE
      [locid, gid
      11,12
      11,15
      22,23
      22,24
      22,25
      22,66
      22,75
      55,76
      ];
      

       

      Find out Gid and create table of rows shown in table below.

       

      resutColsid   locid    gid
      Ra NA,   1   11   12
      Ra NA,   3   22   23
      Ra NA,   3   22   24
      Fin Dk,   5   22   66
      Fin Dk,        5   22 75

       

      Gid is common (intersection) of each sid, locid combination for each row in ref data.

      Result Col is if level4 is null then get one level up, if level 3 is null then get one level up. I have only 5 levels.

       

      Ra NA is at level4 while Fin Dk at level 3.

      Combination of level0&level1&level2&level3&level4&sid&locid colums make unique key.

       

      If I have to do in oracle I would do something like this which will give me intersection of gid.

       

      For data in (select * from  refdata )  loop
          Select data.level0, data.level1, data.level2, data.level3, data.level4, data.sid, data.locid, SidData.gid
              From SidData
             Where siddata.sid=data.sid;
        INTERSECT
         Select data.level0, data.level1, data.level2, data.level3, data.level4, data.sid, data.locid, LocData.gid
            From LocData
              Where LocData.locid=data.locid;
      End loop;
      
      

       

       

      Please let me know if i am missing somthing.

       

      Thank you in advance for help