in a BW transformation we have some logic that does a lookup from DSO data that we have partitioned.
That means we have table X2014, X2013, X2012, X2011 and so on.
For our lookup we only have to look back three years, so this year we have to look in X2014, X2013 and X2012.
next year we have to look in X2015, X2014 and X2013.
The transformation is now changed every year and i am wondering if we can do this any smarter.
A little further explanation; this year it looks like this.
Select data from X2014.
if not found then select data from X2013.
if still not found then select data from X2012.
My question is if it is possible to make this dynamic, so:
Select data from this year
if not found then select data from previous year
if still not found then select data from two year ago.
As far as i can judge normal SQL can not handle variabele tablenames or something like that, but i am curious if anyone can think of a solution for this.