Avoid hardcoded cases by using joins

[hleboeuf]

Hello,

We are working on a project where a SQL developer created plenty of hardcoded cases. Here's an example

CASE WHEN p.[TransportationGroup] IN ('0004', '0005', '0006') THEN '3'
WHEN p.[TransportationGroup] IN ('0007', '0008') THEN '2'
WHEN p.[TransportationGroup] IS NULL THEN NULL
ELSE '1'
END AS [SimplePackingCode]

I would like to avoid such and can quite easily creae a local table (based on csv files) that would look like this.

DOMAIN,TYPE,SOURCE_VALUE,FIELD_1,FIELD_2,FIELD_3

MD,PRD_TRANSGRP_SPACK,"0004","3"

MD,PRD_TRANSGRP_SPACK,"0005","3"

MD,PRD_TRANSGRP_SPACK,"0006","3"

MD,PRD_TRANSGRP_SPACK,"0007","2"

MD,PRD_TRANSGRP_SPACK,"0008","2"

MD,PRD_TRANSGRP_SPACK,"_NULL_","_NULL_"

While playing in the join field that a NULL becomes a _NULL_ value we can solve the NULL problem, but that ELSE is giving me quite some problems...

I know I can solve it in 2 steps, 2 joins, first a left to the local table, then a cross to a single record containing the else and then a calculated column...

But wondering if somebody has a better idea ?

[hleboeuf]

Here is a Sample on how it Could work, no Tables here just sample

[ArcherZhang]

The ABAP logic that pushes down to HANA should be in the right direction, so you may try the CDS function(CASE, WHEN...), if you don`t have CDS, create one... 🙂

[hleboeuf]

Here is a Sample on how it could work, I used no tables so that it works on every SQL, but this is a 2-config-table solution, so quite some work, there are over 100 of such cases in his "solution"

SELECT S1.Product
     , S1.Cat
	 , S3.Final
	 , S4.Final
	 , CASE WHEN S1.Cat IS NOT NULL AND S3.Final IS NULL THEN S4.Final ELSE S3.Final END AS RealFinal
  FROM (SELECT 'X1' AS Product, '0002' AS Cat
        UNION ALL
		SELECT 'X2' AS Product, '0002' AS Cat
        UNION ALL
        SELECT 'X3' AS Product, '0003' AS Cat
        UNION ALL
        SELECT 'X4' AS Product, NULL AS Cat
        UNION ALL
        SELECT 'X5' AS Product, '9999' AS Cat
	   ) S1
       LEFT JOIN (SELECT *
                     FROM (SELECT '0002' AS Cat, '22' AS Final
                           UNION ALL
                           SELECT '0003' AS Cat, '33' AS Final
                           UNION ALL
		                   SELECT '_NULL_' AS Cat, NULL AS Final
	                      ) S2
				  ) S3 ON S3.Cat = COALESCE(S1.CAT,'_NULL_')
       CROSS JOIN (SELECT '_ELSE_' AS Cat, '00' AS Final
				  ) S4