I am facing a challenge that I would like to share with everyone.
Imagine we have a FACT table like:
FACT:
A
B
C
D
E
F
G
1
8
9
3
4
10
1
9
2
7
2
2
2
6
3
3
1
4
4
6
6
4
4
2
3
7
5
6
And also a "Mapping" Table like:
A
B
C
D
E
F
G
OUTPUT
1
3
4
AA
2
6
BB
3
4
CC
4
4
5
DD
Now, I need to retrieve the OUTPUT values in the FACT table. Considering that "blank" values in the mapping table means "regardless the value in the fact table".
The only solution I can think about is using a LOOP to create "all possible combinations of keys" or "all possible mapping tables" since the key to join both table is unknown. In other words, The key might be , A, AB, ABC, ABCD, .... etc. That means we have 7 factorial = 5040 possible keys.
FYI, my real scenario consist in a table with +50k rows and 10 mapping fields (about 4M possible combinations of keys). And a fact table with +50M rows.
