This is probably easy to do (I hope), but my experiments with OUTER JOIN
and CROSS JOIN
etc do not give the results I am after, so here's the question:
I need to do do a projection on fields in a table for each day of the week, where the day of the week represents a certain weight. I assumed a number table would help, but how?
Input table:
Key prc fixweight
1 23.4 0.2
2 78.1 0.6
3 8.7 0.3
Required output (left out: weight calculation on the columns, based on the day of the week):
1 23.4 0.2 1
1 23.4 0.2 2
1 23.4 0.2 3
1 23.4 0.2 4
1 23.4 0.2 5
1 23.4 0.2 6
1 23.4 0.2 7
2 78.1 0.6 1
2 78.1 0.6 2
2 78.1 0.6 3
2 78.1 0.6 4
2 78.1 0.6 5
2 78.1 0.6 6
2 78.1 0.6 7
3 8.7 0.3 1
3 8.7 0.3 2
3 8.7 0.3 3
3 8.7 0.3 4
3 8.7 0.3 5
3 8.7 0.3 6
3 8.7 0.3 7