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
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.
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 (
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 (
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.