Skip to Content

SAP HANA SQL

I Want to concat three fields,but The result of the method shown in the picture1 and picture2 is empty,Please Tell me how to correct it

Such as database table data:

fmsno service_o service_f service_m

fms01 0002 0015

fms02 0008

fms02 0003

the expect results:

fms01 service_type_zh1

fms01 a/c

fms02 b

fms03 a

picture1.png (15.8 kB)
picture2.png (17.8 kB)
Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    Posted on Oct 22, 2019 at 09:47 AM

    Hi Ivan,

    Please check for following SQL statement:

    DO BEGIN
    IT_FMS = SELECT 'fms01' AS fmsno, '0002' AS service_o, NULL AS service_f, '0015' AS service_m FROM DUMMY
    UNION ALL
    SELECT 'fms02' AS fmsno, NULL AS service_o, '0008' AS service_f, NULL AS service_m FROM DUMMY
    UNION ALL
    SELECT 'fms02' AS fmsno, '0003' AS service_o, NULL AS service_f, NULL AS service_m FROM DUMMY
    ;
    
    SELECT fmsno, (CASE WHEN col1 != '' THEN col1 ELSE '' END) || 
    (CASE WHEN (col1 != '' AND col2 != '') THEN '/' || col2 ELSE col2 END) ||   
    (CASE WHEN col3 != '' THEN '/' || col3 ELSE col3 END) as SERVICE_TYPE_ZH1
    FROM
    (
    SELECT c.fmsno,
    CASE WHEN c.service_o IN ('0002', '0003', '0004', '0005', '0006') THEN 'a'
    ELSE ''
    END AS col1, 
    CASE WHEN c.service_f IN ('0008', '0010', '0011') THEN  'b'
    ELSE ''
    END col2, 
    CASE WHEN c.service_m IN ('0015', '0016', '0017', '0018', '0019') THEN  'c'
    ELSE ''
    END AS col3
    FROM :IT_FMS AS C
    )
    ;
    
    END
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 23, 2019 at 07:45 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 22, 2019 at 08:41 AM

    Replace NULL with '' (an empty string).

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Oct 23, 2019 at 06:59 AM

    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,
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.