Skip to Content
avatar image
-3
Former Member

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

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Jan 05 at 06:42 AM



    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;

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

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

      Regards

      JPartheeban

  • Jan 04 at 06:39 PM

    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

    Add comment
    10|10000 characters needed characters exceeded