cancel
Showing results for 
Search instead for 
Did you mean: 

HANA Table Join

Former Member
0 Kudos

Hi Experts,

I've the requirement to join 2 tables and come up with N:N possible combinations. Find below example:

Table1 has Field1 with below values

A

B

C

Table 2 has field 2 with below values:

1

2

3

I need output table which will show the data as below

Field1     Field2

A               1

A               2    

A               3    

B               1

B               2    

B               3 

C               1

C               2    

C               3 

I know the solution using CURSOR -> FOR.. END FOR. Is there any other effective way of achieving the same.

Thanks,

Milind

Accepted Solutions (1)

Accepted Solutions (1)

former_member200930
Participant
0 Kudos

Hi Milind,

Please check below solution for your problem.

Step 1 : Create tables in SAP HANA.

Create column table TBL_A ("NAME" Varchar(1));

Create column table TBL_1 ("VALUE" tinyint);

Step 2 : Insert values in abovementioned tables.

insert into TBL_A values('A');

insert into TBL_A values ('B');

insert into TBL_A values ('C');

insert into TBL_1 values(1);

insert into TBL_1 values (2);

insert into TBL_1 values (3);

Now, you  will notice that, we don't have common column(s) to make join and get desired output in SAP HANA modeling.

Modeling

Step 3 :

Add two aggregation node and try to place respective tables in it.

Aggregation_ 1 :  Try to create DUMMY as a calculated column

Aggregation_2 : Try to create DUMMY as a calculated column

Step 4 : Please add '1' as a value in DUMMY column as per below image

Step 5 : create Inner join on DUMMY column

Step 6 : As, I have taken both fields as a attribute in final node. I have created counter for measure.

Step 7 : Activate your view and see the final output.

You can skip counter column, if you don't want.

I hope, this will solve your problem.

Regards,

Vikram Divekar

Answers (1)

Answers (1)

former_member185511
Active Participant
0 Kudos

Select t1.FIELD1, t2.FIELD2 from table1 as  t1

cross join table2 as t2;