Skip to Content
avatar image
Former Member

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.

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    avatar image
    Former Member
    Jan 13, 2015 at 04:56 AM

    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.PNG (23.5 kB)
    2.PNG (7.9 kB)
    3.PNG (12.5 kB)
    4.PNG (58.3 kB)
    5.PNG (14.9 kB)
    6.PNG (36.6 kB)
    7.PNG (20.8 kB)
    8.PNG (43.5 kB)
    9.PNG (15.9 kB)
    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 13, 2015 at 06:12 AM

    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

    Add comment
    10|10000 characters needed characters exceeded