Simplifying repetitive SQL

[#1]

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?

[lbreddemann · Accepted solution]

This is a classic example of what is otherwise known as "pivot" query.
While SAP HANA flowgraphs/Smart Data Quality provide modelling tools for just this (and you may very well have a look at this) HANA SQL doesn't come with a "PIVOT" keyword for this operation (it's not part of standard SQL).

That means, as a developer you have little choice here and write repetitive code.

One option to avoid this would be meta-programming. Write a program that generates the code for you. If you find yourself doing this kind of block-copy-coding a lot, this might be worthwhile.

For a once-off with limited number key figures (anything under, say 200) this probably won't pay off.

Therefore I'd aim at making this as easy as possible.

To rewrite the construct I'd change the UNIONs into UNION ALLs and turn the inline select into an outer join like so:

    select /* ACT 1*/
        k.f1, k.f2, k.f3
        , IFNULL (l.mapped , 'ACT1') as account_name
        , ACT1 as    account_value
    from kftab k 
    left outer join lookup l
    on (k.f1, 'ACT1') = (l.f1, l.act)
    where 
        k.ACT1 != 0.0
UNION ALL
    select /* ACT 2*/
           k.f1, k.f2, k.f3
        , IFNULL (l.mapped , 'ACT2') as account_name
        , ACT2 as    account_value
    from kftab k 
    left outer join lookup l
    on (k.f1, 'ACT2') = (l.f1, l.act)
    where 
        k.ACT2 != 0.0

union all
...

This makes the SELECT list a bit easier to read (at least to me) and puts the JOIN condition where I would expect it.
Initially I thought that this would also lead to better performance as the inline select appears to be executed for every record, while the outer join is done once for the whole selected data set. This, however, is not the case as the HANA optimizer (tested on HANA 2 SP 03) automatically rewrites the query, so that both approaches lead to the same execution plan (outer join).

Using UNION ALL is a bit cheaper than UNION since the final result set doesn't require an additional DISTINCT operation anyway (i.e. the UNION parts here are by definition not overlapping).

Now, that looks still pretty ugly and the main part really is the IFNULL and the JOIN construct.

This can be hidden in a scalar user defined function (sUDF), similar to this:

create function remap_act_name (IN F1 varchar(20), IN ACT nvarchar (20)) 
returns act_name varchar(20) deterministic
as 
begin
    select coalesce (max(mapped), :ACT) into  act_name
    from (
          select mapped 
          from lookup 
          where 
                  f1 = :F1
             and act = :ACT
         union all
         select to_varchar(null) as mapped
         from dummy);
end;    

This SELECT looks a bit complex, but the SELECT FROM DUMMY part is just there to ensure that at least one record gets returned (otherwise non-matches lead to a "no data found" error) .

With this function in place we can get rid of all the joins and the query looks like this:

    select /* ACT 1*/
        k.f1, k.f2, k.f3
        , remap_act_name (k.f1, 'ACT1') as account_name
        , ACT1 as    account_value
    from kftab k 
    where 
        k.ACT1 != 0.0
union all
    select /* ACT 2*/
        k.f1, k.f2, k.f3
        , remap_act_name (k.f1, 'ACT2') as account_name
        , ACT2 as    account_value
    from kftab k 
    
    where 
        k.ACT2 != 0.0
union all
...

This improves the readability of the statement quite a bit and keeps the lookup logic in a single place. Not too bad.

Of course, nothing comes for free, so such an approach does come with worse performance characteristics than the pure SQL one.

To provide some perspective: while pure (and convoluted) SQL took 40ms and 1.6 MB to work through my minimal test data set, the UDF approach used about double the time and 200 MB of RAM.
These numbers don't mean anything, really, as they are totally dependent on the amount of data to work on but should illustrate that there is an effect when using UDFs.
If the gained maintainability of the code is worth this "cost" needs to be judged case by case. I'd probably go with the UDF approach if there is no strong incentive to save computing resources.

[matt · Accepted solution]

It's probably going to be a one off,

In fact, I need UNION ALL; because in later forms of the clause, we have things like VV88n going to VV88X (if there's no exception mapping). And I do need !=0 rather than >0 - there may be negative numbers.

As this is part of an AMDP in a BW transformation, the duplicate entries into the ADSO are automatically summed.

The HANA release I'm working on, doesn't yet have UDFs, so I'm stuck with pure SQL - though performance really is vital for this solution. It's a POC that pushdown to HANA works better than the current ABAP. Certainly, the SQL approach turns out simpler than the ABAP solution.

[matt · Accepted solution]

In the end, I wrote an ABAP program to generate the SQL. It writes the SQL to a stored procedure in the /1BCAMDP/ namespace. This is necessary, due to security constraints.

The HANA Expert Routine AMDP then calls this stored procedure.

It works quite nicely. Or at least it did, until we upgrade to HANA 2.0. Now it runs very very slowly. It's being investigated...