Hello all,
I have a table with fields as follow
The first table is my main table, with customer ID, references, and different hierarchy levels.
Table A:
HIE1 HIE2 HIE3 HIE4 HIE5 CUSTOMER REFERENCE
A A1 A2 A3 A4 000100 003
B B1 B2 B3 B4 000101 004
And another table with only a single hierarchy field (my key with table A), department field and reference. My main problem, hierarchy field presented here could be possibly joined either with HIE1 or HIE2 or HIE3 on table A.
Table B :
HIE REFERENCE DEPT
A1 003 GCY
B2 004 FRT
Result i'd like to have:
HIE1 HIE2 HIE3 HIE4 HIE5 CUSTOMER REFERENCE DEPT
A A1 A2 A3 A4 000100 003 GCY
B B1 B2 B3 B4 000101 004 FRT
I can not find how to easily make a link btw two tables with one field that possibly be in different column.
in Table B, the hierachy key not indicate the level of hierarchy present in table A.
Is anyone has a solution ? Would be very nice. Hope i was clear enough
