How to use MAP function in SQL script

In a previous post, "How to Use Window Functions in SQL Script", we introduced the window function that allows you to generate partitions within the SELECT command.

In this article, we will introduce MAP function, another handy function that allows you to map simple mapping rules. This function searches for an expression within a set of search values and returns the corresponding result. It is comparable to IF/ELSE or CASE statements in ABAP.

Syntax

The syntax of the MAP function looks like this:

MAP (<expression>, <search>, <result> [{, <search>, <result>}...] [, default_result])

You can define any number of search and result pairs. If the searched value is not found and a default result has been defined, the function outputs the predefined result. If no default result has been defined, the function outputs "NULL". Search values and corresponding results are output in pairs.

Examples

Let's consider a simple example for clarification. With the following code, we search for the values 0, 1, 2, and 3 in expression 3. If the value 0 is found, “Zero” is returned. If the value 1 is found, “One” is output. And so on.

SELECT MAP(3, 0, 'Zero', 1, 'One', 2, 'Two', 3, 'Three', 'Default') "map" FROM DUMMY;

Since we have defined only one value, 3, in the expression, the 3 will also be found and "Three" will be output.

001-value-found_map function

Let's consider the next example. Now we are looking for the same values, but in expression 815.

SELECT MAP(815, 0, 'Zero', 1, 'One', 2, 'Two', 3, 'Three', 'Default') "map" FROM DUMMY;

Since none of the search terms were found, the predefined result, meaning "Default", is output.

002-value-not-found-default-value_map function

Now let's consider the code where no default value has been defined.

SELECT MAP(815, 0, 'Zero', 1, 'One', 2, 'Two', 3, 'Three') "map" FROM DUMMY;

Since no default value was specified, the initial value "NULL" is output.

003-initial-value-null_map function

Increase the performance of your BW with SQLScript!
Click here for the whitepaper!

In SAP context, you can use the Map function to assign categories to product master data on the fly:

SELECT "PRODUCT",

        MAP("PRODUCT",

        'SM', 'Smartphone',

        'SP', 'Smartphone',

        'TM', 'Tablet',

        'TP', 'Tablet') as Category

FROM "/BI0/MPRODUCT"

004-category_map function

Let's consider another example where the Map function is used to output the delivery status of an order.

SELECT

        "PO_NUMBER" AS "Purchase_Order",

        "ACT_DL_DTE" AS "Delivery_Date",

        MAP("ACT_DL_DTE",

        '00000000', 'open',

        current_date, 'delivered today',

        'delivered') AS "Delivery_status"

FROM "/BIC/AZD023R0017"

GROUP BY

        "PO_NUMBER","ACT_DL_DTE"

Please note that the delivery date # (not assigned) is stored internally in SAP as 000000. This means that undelivered orders have the date 00000000. These are assigned the status "open". All orders delivered on today's date are output with the delivery status "delivered today". The current date is determined by the SQL function current_date. The orders with all other delivery dates are marked as "delivered".

005-delivery-date_map function

MAP function - Our Summary

With the SQL MAP function, you have a useful tool in your toolbelt that allows you to implement simple mapping rules.

Do you have questions about SAP HANA SQLScript ? Are you trying to build up the necessary know-how in your department or do you need support with a specific question? We are happy to help you. Request a non-binding consulting offer today!