0

# Left Outer Join in HANA star join calculation view

May 10, 2017 at 06:53 AM

737

Former Member

Hi,

I have created a calculation view with data category as "Cube" and Star Join Enabled.

The fact table(Table A) has been projected to Star Join node and then in the star join node it has been joined with CV dimensional view which is created using Table B. Now the table A and Table B has been Left outer Joined using the columns, i.e. ( AA, AB, AC).

Issue:

When i query all the join columns between Table A and TableB

then the data in the output is coming as

When Matching records are there between both tables,then all the column values are fine.

When Matching records are not there, i.e record is there in Table A and not there in Table B and if i query all the join columns between A and B, the B table join column values are also coming same as Table A.

TableA.AA TableA.AB TableA.AC

1 1 1

Table B.AA,TableB.AB,TableB.AC

Null Null Null

Final Output in CV view query is coming as

Select TableA.AA, TableA.AB,TableA.AC,Table B.AA,TableB.AB,TableB.AC

from CV view

TableA.AA TableA.AB TableA.AC Table B.AA TableB.AB TableB.AC

1 1 1 1 1 1

Could some one please let me know why it is coming like this.

And similar left outer join in CV view is not producing the same result.

Thank you,

RP

Former Member

Do we have an answer for this? We are facing the same issue.

Just to summarize -

We are using HANA 1.0 SPS12. We have one CV with type as star join. We have inner join between few dimension CVs and transaction table and left outer join between transaction table and few dimensions -

1. Inner join works fine
2. Left outer join works fine for matching records.
3. In case of unmatched records, join columns show same value as of transaction table. it is expected to have null values for unmatched rows.

Best Regards

Former Member

Hi,

I think that's how the star join works. In case of your requirement, move out the Leftouter Join with out of Star Join and you need to do the required calculation on top of it.