Skip to Content
0
Aug 15, 2018 at 11:43 AM

Simplifying repetitive SQL

278 Views Last edit Aug 15, 2018 at 02:25 PM 4 rev

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?