HANA SQL - range Low and High value to be expand to individual values

[johnsubin]

I am trying to implement a SQL logic to achieve the following Final table. Please suggest.

[KonradZaleski · Accepted solution]

I don't know whole structure of your table but I prepared an example which should work for the dataset which you provided (I used T503 and AGR_1251 as datasources). First I splitted these two into those having characters (CHAR) and those with numbers only (INT) and assigned them to variables.

Then joined those having characters together separately from those having numbers (otherwise BETWEEN function will not give proper output when you would mix character with number).

At the end I UNION both datasets:

DO
BEGIN

T503_CHAR =
             SELECT * 
             FROM T503 
             WHERE "PERSK" like_regexpr '[A-Z]';

T503_INT =
             SELECT * 
             FROM T503 
             WHERE "PERSK" NOT like_regexpr '[A-Z]';

AGR_1251_CHAR =
             SELECT * 
             FROM AGR_1251 
             WHERE "Low" like_regexpr '[A-Z]';

AGR_1251_INT =
             SELECT * 
             FROM AGR_1251 
             WHERE "Low" NOT like_regexpr '[A-Z]';

SELECT
   T2."Role",
   T2."Field",
   T1."PERSK" AS "Value"
FROM
   :T503_CHAR T1
   JOIN :AGR_1251_CHAR T2 ON T1."PERSK" BETWEEN T2."Low" and T2."High"

UNION

SELECT
   T2."Role",
   T2."Field",
   T1."PERSK" AS "Value"
FROM
   :T503_INT T1
  JOIN :AGR_1251_INT T2 ON T1."PERSK" BETWEEN T2."Low" and T2."High";
END

My output looks as follows:

[johnsubin · Accepted solution]

Hi Konrad Załęski,

It works perfectly . Thank you very much. It has been very helpful.

[KonradZaleski · Accepted solution]

Hi Subin. If this solves you issue, please accept my answer.
Thanks.