Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Condition Join

Need Help Please

I am trying to join 2 tables in Calculation View of SAP HANA

1) Salary Table

Salary_Slab, Salary_Start, Salary_End
A,   10000,  20000
B,   20000,  30000
C,   30000,  40000
D,   40000,  50000

2) Employee Table

Employee_Number, Salary
01,   15000
02,   18000
03,   32000
04,   45000


The Output I need is the Employee Details with the corresponding Slab


Employee_Number, Salary, Slab

01,   15000, A
02,   18000, A
03,   32000, C
04,   45000, D


Can you please suggest how to Achieve this without writing Scripts.

Tags:
Former Member
Former Member replied

Hi,

I've made it using calculation view without writing SQLScript.

You can follow the steps below.

1. Create some tables. For simplicity, I just used SYSTEM user to do all the things, creating tables, modeling. It's better to create another user to do it.

CREATE COLUMN TABLE SALARY (

  SALARY_SLAB VARCHAR(1),

  SALARY_START INTEGER,

  SALARY_END INTEGER

);

INSERT INTO SALARY VALUES ('A', 10000, 20000);

INSERT INTO SALARY VALUES ('B', 20000, 30000);

INSERT INTO SALARY VALUES ('C', 30000, 40000);

INSERT INTO SALARY VALUES ('D', 40000, 50000);

CREATE COLUMN TABLE EMPLOYEE (

  EMPLOYEE_NUMBER VARCHAR(2),

  SALARY INTEGER

);

INSERT INTO EMPLOYEE VALUES ('01', 15000);

INSERT INTO EMPLOYEE VALUES ('02', 18000);

INSERT INTO EMPLOYEE VALUES ('03', 32000);

INSERT INTO EMPLOYEE VALUES ('04', 45000);

GRANT SELECT ON SCHEMA SYSTEM TO _SYS_REPO WITH GRANT OPTION;

2. Create a calculation view, choose dimension in data category. Add EMPLOYEE table to Projection_1 node. Create a calculated column DUMMY, constant value 1.

3. Add SALARY table to Projection_2 node. Also create a calculated column DUMMY.

4. Create Join_1 node. Inner join two projections with DUMMY, so now cross-join.

5. Add Projection_3 node. Add filter to SALARY with filter expression.

6. Connect Projection_3 with the final Projection node.

That's it. Hope you can make it.

Best regards,

Wenjun

1 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question