cancel
Showing results for 
Search instead for 
Did you mean: 

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

0 Kudos

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', '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 19442108077 1 19442108077 QUALIFIER 1

04-01-2018 MASTER 561 561 QUALIFIER NULL

04-01-2018 MASTER 562 562 QUALIFIER NULL

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

Regards

JPartheeban

Accepted Solutions (0)

Answers (2)

Answers (2)

akshinde
Participant
0 Kudos



U can try this , this is most easy and clean code, made up with CASE Statement



SELECT

BUSINESSDATE, DESC, ITEMID, ITEMQUALIFIER, EAN11, DESCRIPTION

FROM MASTER INNERJOIN DETAIL ON

MASTER.ITEMID=CASE WHEN MASTER.ITEMQUALIFIER =1 THEN DETAIL.EAN11

ELSE DETAIL.MATNR END;

0 Kudos

THANKS FOR THE QUICK RESPONSE for both of you. Both methods are fine.

Regards

JPartheeban

pfefferf
Active Contributor
0 Kudos

I'm not sure what you are really asking here, but if it is just that you do the join depending on the value of the ITEMQUALIFIER column, you can use both selects you have already + enhance it with the corresponding where clauses (... where ITEMQUALIFIER = '1' for the first select, ... where ITEMQUALIFIER = '' for the second select). The results of both selects can be unioned by a UNION to have one result set.

Regards,
Florian