Condition Join

[former_member624795]

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.

[Former Member · Accepted solution]

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

[former_member624795 · Accepted solution]

Hi Wenjun - Very Helpful.. thanks..

[Former Member]

Hi Charit Pal Dhawan,

I did the same way as Wenjun Zhou did except for that filter part, I used the calculated column.

But I feel Wenjun Zhou solution is better than mine.

Happy modeling!

Thanks

Monissha

[former_member624795]

Thanks Monissha