on 01-04-2018 6:35 PM
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
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;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.