Intersection of data

[#1]

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.

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

[#2]

Any one please?

[#3]

Any Help please

[whiteline]

refdata:
LOAD
      *, 
      if(not len(level4)=0, level4,
      if(not len(level3)=0, level3)) as resultCol ;
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
];
SidLocData:
LOAD * INLINE
[sid, gid
1,12
1,123
3,23
3,24
5,66
5,77
5,75
];
inner join(SidLocData)
LOAD * INLINE
[locid, gid
11,12
11,15
22,23
22,24
22,25
22,66
22,75
55,76
];