SAP HANA SQL

There are obviously many different ways of solving this problem.

From my point of view, including the logic for the value mapping and the formatting into the select statement is the least reusable option. It also makes it hard to understand what is going on and to check for correctness.

An alternative approach could be to indeed use a scalar function to encapsulate the logic for both steps. Something like this could do:

drop  function getServiceTypes;
create function getServiceTypes ( serv_o NVARCHAR(5)
                                , serv_f NVARCHAR(5)
                                , serv_m NVARCHAR(5))
returns serviceTypes NVARCHAR(20)
as
begin
declare comp_a nvarchar(5);
declare comp_b nvarchar(5);
declare comp_c nvarchar(5);
declare num_components int := 0;

    if :serv_o = '0002' 
    or :serv_o = '0003'
    or :serv_o = '0004'
    or :serv_o = '0005'
    or :serv_o = '0006' then
        comp_a := 'a';
        num_components := :num_components+1;
    else
        comp_a := '';    
    end if;

    if :serv_f = '0008' 
    or :serv_f = '0010'
    or :serv_f = '0011' then
        comp_b := 'b';
        num_components := :num_components+1;
    else
        comp_b := '';    
    end if;

    if :serv_m = '0015' 
    or :serv_m = '0016'
    or :serv_m = '0017'
    or :serv_m = '0018'
    or :serv_m = '0019'then
        comp_c := 'c';
        num_components := :num_components+1;
    else
        comp_c := '';    
    end if;
    
    /* alternative way to find the number of non-empty components
    -- each component is a single character, so the length gives us the number of components
    -- we only need forward slashes for 2 or 3 component output
    num_components := length(:comp_a || :comp_b || :comp_c);
    */
    
    serviceTypes :=    :comp_a || MAP(:num_components, 2, '/', 3 ,'/', '') 
                    || :comp_b || MAP(:num_components, 3 ,'/', '')
                    || :comp_c;

end;

The code is very straight forward and boring - which is what you want. It is obvious what is going on here.

For all three input strings the matching is done (with HANA 2 one could also use the IN () operator to make the code look nicer) by an IF clause. Whenever a match is found, the target component is filled with the indicator (a, b or c) and a counter variable keeps track of how many components are filled so far. This counter is later used to determine which of the separation slashes (/) need to be included in the return string.

Finally, the return string is a simple string concatenation with the MAP() function deciding on the separators.

With this scalar function, the original select looks like this:

select fmsno
     , getServiceTypes(service_o, service_f, service_m) as servicetype
from it_fms;

Both the select statement as well as the function are much easier to read and understand now and if the mapping should be changed at some point, only the function needs to be updated - not the select statement.

Replace NULL with '' (an empty string).

Hi Ivan,

I noticed that you have written your code as below

CONCAT('/'
        CONCAT(CASE WHEN c.service_f IN ('0008', '0010', '0011')
                    THEN 'b'
                    ELSE NULL
                    END,

Instead try this piece of code

CONCAT( CASE WHEN c.service_f IN ('0008', '0010', '0011')
             THEN '/b'
             ELSE NULL
             END,