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
