Hana Cloud SQL script code improvement tips

[aleksandrskerauts]

Dear experts

I have a couple of questions related to Hana SQL script :

01. Is there a smart way to replace multiple CASE WHEN IS NOT NULL THEN A ELSE B

see attachment01

sql-code01.jpg

02. Is there a smart way to replace multiple CASE WHEN A THEN B

WHEN C THEN D

WHEN L THEM M

see attachment 02

sql-code02.jpg

Thanks

[Cocquerel]

Instead of using CASE and LIKE, you could use MAP and SUBSTR_REGEXPR like in below example

MAP(SUBSTR_REGEXPR('RED|YELLOW|GREEN' IN  BULK_CHANGE_CONTROL_INDICATOR),
     'RED',3,
     'YELLOW',2,
     'GREEN',1,
     0) AS BULK_CHANGE_CONTROL_INDICATOR

and instead of CASE WHEN A IS NOT NULL THEN A ELSE B, you can use IFNULL like in below example

IFNULL(SUQ_COUNTRIES,'N/A') AS SUQ_COUNTRIES