ABAP SQL - COALESCE Function

-

Hello Everyone!👋

SQL COALESCE Function in ABAP

The COALESCE function is extremely useful when working with optional or missing data. It accepts a list of at least two (and up to 255) arguments — COALESCE(arg1, arg2, …, argn) and returns the first non-null value in the list. If all values are null, it returns the last argument.

Here is a small example, We have three tables:
𝗹𝘁_𝘀𝗮𝗹𝗲𝘀𝗼𝗿𝗱𝗲𝗿𝘀: Contains sales orders with net values and customer IDs.

DATA(lt_salesorders) = VALUE tt_salesorder(
( salesorder = '1' netvalue = '100.00' customer = 'CUST_1' )
( salesorder = '2' netvalue = '100.00' customer = 'CUST_2' )
( salesorder = '3' netvalue = '100.00' customer = 'CUST_3' ) ).
𝗹𝘁_𝗰𝘂𝘀𝘁𝗼𝗺𝗲𝗿_𝗼𝗻𝗲 and 𝗹𝘁_𝗰𝘂𝘀𝘁𝗼𝗺𝗲𝗿_𝘁𝘄𝗼: Two datasets with customer names and address IDs.
DATA(lt_customer_one) = VALUE tt_customer(
( customer = 'CUST_1' customername = 'Customer 1' addressid = 1 ) ).

DATA(lt_customer_two) = VALUE tt_customer(
( customer = 'CUST_2' customername = 'Customer 2' addressid = 2 ) ).
𝗹𝘁_𝗰𝘂𝘀𝘁𝗼𝗺𝗲𝗿_𝗮𝗱𝗱𝗿𝗲𝘀𝘀: Stores addresses linked by addressid. 
DATA(lt_customer_address) = VALUE tt_customer_address(
( addressid = 1 address = 'Sitheri, Cuddalore' )
( addressid = 2 address = 'Velluvadi, Perambalur' ) ).

Some customers exist in lt_customer_one and others in lt_customer_two. Using COALESCE, we can fetch the appropriate customer name and address ID regardless of where the data resides. Once we get the non-null addressid, we join it with the customer address table to retrieve the full address. 


SELECT FROM @lt_salesorders AS a
LEFT OUTER JOIN @lt_customer_one AS b ON b~customer = a~customer
LEFT OUTER JOIN @lt_customer_two AS c ON c~customer = a~customer
LEFT OUTER JOIN @lt_customer_address AS d ON d~addressid = COALESCE( b~addressid, c~addressid )
FIELDS a~salesorder,
a~netvalue,
a~customer,
COALESCE( b~customername, c~customername ) AS customername,
d~address AS customeraddress
INTO TABLE @DATA(lt_salesorders_with_customer).

✔ Output

Notes: This is an alternative for a case expression. and this function is available in CDS, OPEN SQL(ABAP 7.4+) and AMDP.

For more information, please check the official documentation: ABAP Help

Comments

Post a Comment

Popular posts from this blog

Fiori URL Generation and Navigation Using CL_LSAPI_MANAGER

-
Hello Everyone!👋 Fiori URL Generation and Navigation Using CL_LSAPI_MANAGER CL_LSAPI_MANAGER - This class allows you to easily generate SAP Fiori Launchpad URLs or trigger intent-based navigation. You’ll find it useful when you need to: Launch Fiori apps from the SAP GUI Generate dynamic Fiori URLs using semantic objects, actions, and parameters Share specific Fiori app links via email Navigate directly to transactions, and more DATA(lo_lsapi) = cl_lsapi_manager=>get_instance( ). " Generate URL to launch a Fiori app DATA(lv_flp_app_url) = cl_lsapi_manager=>create_flp_url( object = 'MaintenanceObject' " Semantic Object action = 'listFunctionalLocStructure' " Action system_alias = 'LOCAL' " SM59 Alias for FES parameters = VALUE #( name = 'DY_TPLNR' value = '0001' ) ). " Generate URL to launch a tran...
Read more

RAP - Real Time UI Updates Using Event-Driven Side Effects

-
Hello Everyone! 👋 Real-Time UI Updates in RAP Using Event-Driven Side Effects We've all come across situations where something changes in the backend, and we want those updates to automatically reflect in the UI without manually refreshing the page. In traditional setups, we used ABAP Channels for this but how can we achieve the same in RAP? Here's a simple example: I'm updating the travel status in one class/report, while the app is open in parallel. As soon as the status is updated in the backend, it automatically reflects in the UI no refresh needed! This is made possible through Event-Driven Side Effects . This is useful when: Background jobs update data We want to notify users with messages in real time Multiple users are working on the same data simultaneously Step 1 – Add the event-driven side effects in the BDEF managed with additional save .... .. define behavior for YR_YABS_TRAVEL...
Read more

ABAP - Create CSV Files Easily Using CL_CSV_FACTORY

-
Hello Everyone! 👋 Create CSV Files Easily in ABAP Using CL_CSV_FACTORY In many scenarios, we need to download internal table data in CSV format. Often, the requirements vary for example, using different field separators such as semicolon (;), comma (,). Additionally, we may need to include a header row and format fields like date, time, or timestamp. All of this can be handled very easily using the CL_CSV_FACTORY class. The best part is that this class is also available in the SAP Public Cloud. Step 1 – Select the Data SELECT FROM vbak FIELDS vbeln, vbtyp, erdat INTO TABLE @DATA(lt_test) UP TO 10 ROWS. Step 2 – Create a Simple CSV with Header This example generates a CSV using a semicolon as the delimiter and includes the header row automatically. DATA(lv_xstring_v1) = cl_csv_factory=>new_writer( )->set_delimiter( ';' )->set_write_header( abap_tr...
Read more