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

# Intersection of data

Hello Everyone.

I need help creating data table.

```refdata:
[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:
gid as gidTemp1;
[sid, gid
1,12
1,123
3,23
3,24
5,66
5,77
5,75
];

LocData:
gid as gidTemp2;
[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.

 resutCol sid 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

• ###### Re: Intersection of data
```refdata:
*,
if(not len(level4)=0, level4,
if(not len(level3)=0, level3)) as resultCol ;
[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
];

SidLocData:
[sid, gid
1,12
1,123
3,23
3,24
5,66
5,77
5,75
];

inner join(SidLocData)
[locid, gid
11,12
11,15
22,23
22,24
22,25
22,66
22,75
55,76
];
```