Joins in Logical View

[srikanthbwbw]

Hi Friends,

I am new to SAP HANA.
1. I created attribute view on MARA table (ZMAT_ATTV) and selected MATNR, MEINS AND MATKL. Activated and did the data preview-- Successful

2. Created Analytical view on EKPO table (ZPUR_ANAV) and selected EBELN, EBELP, MATNR, MEINS AND MENGE. Activated and did the data preview--successfull.

Now in logical view I added ZMAT_ATTV and linked MATNR to MATNR and selected RIGHT OUTER JOIN with cardinality 1:n. Data preview shows results. Next i selected LEFT OUTER JOIN with cardinality 1:n, Here no change in result.
I selected all types of joins and cardinalities but result remains the same.

if i select left outer join, then i should get only 1 record per material as MARA holds only one record but tats not happening. Am i missing something.

Thanks
Sri

[patrickbachmann · Accepted solution]

So I think what you're saying is for a specific Purchase Order (ie: 1000) you have a single material such as XYZ and when you join it with MARA you are getting XYZ showing multiple times?  Can you click on SQL window and do;

select * from MARA where MATNR = 'XYZ'

Do you see multiples?

Now select * from EKPO where EBELN = '1000'

Do you see multiples?   I'm wondering if you've included all the fields which will make that PO unique?  ie: Maybe its same material but different PLANT?  Then you would need to add PLANT etc.

-Patrick

[srikanthbwbw · Accepted solution]

Hi Patrick,

Thanks for reply.

I MARA table i have material "XYZ"

In EKPO i have several purchase orders that have material "XYZ".

Ex:

PO-------ITEM------MATNR--------MENGE

1001------10-----------XYZ.............100

1001------20-----------ABC.............45

1002------10-----------XYZ.............100

1002------20-----------JKL.............165

1003------10-----------XYZ.............98

Now in MARA table i have only one entry for XYZ

In logical view EKPO as left table and MARA as right table

I did right outer join with 1:N cardinality and N:1 cardinality. Results are same.

For N:1 cardinality, I am expecting only one row in the data preview as it is right out join. Is my assumpion correct??

-------------------

Sorry misunderstood the concept. Now got the answer.

Thanks

Sri

[former_member182337 · Accepted solution]

Hi Sri,

May be you can provide the answer here for the benefit of others.

Regards,

Prem

[srikanthbwbw · Accepted solution]

Hi Prem,

My understanding was wrong in regards to cardinality.

I assumed when i linked 1:N, I will get only one record from right table (when using right outer join). its wrong. I will get all the records from right table if material on right table(SALES) = material on left table (MARA)