on 01-20-2016 5:18 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Select t1.FIELD1, t2.FIELD2 from table1 as t1
cross join table2 as t2;
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
87 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.