In an AMDP, I am transforming data in BW from a key figure model ADSO, to an account model ADSO. I have SQL of this form:
select field1, field2, field3,
IFNULL ( ( select map from LOOKUP where field1 = :intab.field1
AND account = 'VV000' ),
'VV000' ) as account,
vv000_value as value
from :intab
where vv000_value > 0
UNION
select field1, field2, field3,
IFNULL ( ( select map from LOOKUP where field1 = :intab.field1
AND account = 'VV001' ),
'VV001' ) as account,
vv001_value as value
from :intab
where vv001_value > 0
UNION
...
Each VVnnn is an account. For some accounts, they need to be mapped in the account model to a different name. By default though, the same account name is used.
For each account, I have a separate UNION. There can be between 20 and 30 key figures in the source. Is there anyway of removing this code duplication and simplifying the SQL?