Table Joins with two different mapping in IF then ELSE in HANA Script Based Calculation View

142 Views

Hi

I have to sort out some logic to display the (final output) in the Hana Script based view. Can you please help here

use case;

CREATE TABLE MASTER ( BUSINESSDATE DATE, DESC VARCHAR(20), ITEMID VARCHAR(18), ITEMQUALIFIER CHAR(1) );

INSERT INTO MASTER VALUES ('SYSDATE', 'MASTER', '000000000003110171', '2');

INSERT INTO MASTER VALUES ('SYSDATE', 'MASTER', '000000000007845126', '2');

INSERT INTO MASTER VALUES ('SYSDATE', 'MASTER', '000000000008606004', '2');

INSERT INTO MASTER VALUES ('SYSDATE', 'MASTER', '19442108075', '1');

INSERT INTO MASTER VALUES ('SYSDATE', 'MASTER', '19442108076', '1');

INSERT INTO MASTER VALUES ('SYSDATE', 'MASTER', '19442108077', '1');

INSERT INTO MASTER VALUES ('SYSDATE', 'MASTER', '561', '');

INSERT INTO MASTER VALUES ('SYSDATE', 'MASTER', '562', '');

CREATE TABLE DETAIL ( EAN11 VARCHAR(18), MATNR VARCHAR(18), DESCRIPTION VARCHAR(20) );

INSERT INTO DETAIL VALUES ('000000000003110171', '007', 'QUALIFIER 2');

INSERT INTO DETAIL VALUES ('000000000007845126', '007', 'QUALIFIER 2');

INSERT INTO DETAIL VALUES ('007', '19442108076', 'QUALIFIER 1');

INSERT INTO DETAIL VALUES ('007', '19442108077', 'QUALIFIER 1');

INSERT INTO DETAIL VALUES ('561', '007', 'QUALIFIER NULL');

INSERT INTO DETAIL VALUES ('562', '007', 'QUALIFIER NULL');

my script wants to HANDLE the below logic and needs to store in the local table variable.

if ITEMQUALIFIER is 1 then

local_table_variable =

SELECT

BUSINESSDATE, DESC, ITEMID, ITEMQUALIFIER, EAN11, DESCRIPTION

FROM MSTER

INNER JOIN DETAIL ON MSTER.ITEMID = DETAIL.EAN11;

ELSE

local_table_variable =

SELECT

BUSINESSDATE, DESC, ITEMID, ITEMQUALIFIER, MATNR AS EAN11, DESCRIPTION

FROM MSTER

INNER JOIN DETAIL ON MSTER.ITEMID = DETAIL.MATNR;

END IF;

finally my output should be like below (final output)

BUSINESSDATEDESC ITEMID ITEMQUALIFIEREAN11 DESCRIPTION

-------------------------------------------------------------------------------------------------------------------

04-01-2018 MASTER 000000000003110171 2 000000000003110171 QUALIFIER 2

04-01-2018 MASTER 000000000007845126 2 000000000007845126 QUALIFIER 2

04-01-2018 MASTER 000000000008606004

04-01-2018 MASTER 19442108075

04-01-2018 MASTER 19442108076 1 19442108076 QUALIFIER 1

04-01-2018 MASTER 19442108077 1 19442108077 QUALIFIER 1

04-01-2018 MASTER 561 561 QUALIFIER NULL

04-01-2018 MASTER 562 562 QUALIFIER NULL

-------------------------------------------------------------------------------------------------------------------

Regards

JPartheeban