Hana Calculation View conditional join

[Former Member]

Hi All,

I have the following requirement where i have to join below three fields to get the PURCHASING_VENDOR & PUR_VENDOR_DESCRIPTION from table 2

Table A MANDT WERKS LIFNR LTSNR ZZ_SUB_TTL100 Table B MANDT SHIP_LOCATION DC VSR PURCHASING VENDOR PURCHASING VENDOR DESCRIPTION

TABLE1.WERKS = TABLE2.DC

TABLE1.LIFNR=TABLE2.SHIP_LOCATION

TABLE1.LTSNR=TABLE2.VSR

For some scenarios, DC in the table2 is not maintained I.e DC is null. in that case whenever DC is null, can we only make join on ship_location & VSR to get Purchasing_vendor ? How can we achieve this in HANA Cal.view?

Any advice will be much appreciated.

Thanks,
Sudheer

[KonradZaleski]

Let me know if my answer solves your issue. If yes, please mark it as the answer.

[KonradZaleski · Accepted solution]

Hi Sundheer,

Generally for conditional join I would recommend create SQL Table Function, because of performance. If you really want to apply this logic graphically here is the approach:

Create to separates flows:

1. Create inner join between PURCHASING_VENDOR & PUR_VENDOR_DESCRIPTION using fields which you mentioned to retrieve VENDOR data:

2. Create exactly the same connection but using LEFT JOIN instead:

After that join create projection where you filter all the records having null decription:

So now you have a list of records for which you are missing VENDOR description. Now you can add join with different join conditon (skipping DC field):

3. At the end make union of these two data flows

As a final result you will have all the decriptions for both join conditions.

[Loed · Accepted solution]

Hi konrad,

How can you convert your answer in SQL script view?

I have similar scenario but wanted to do it in SQL script view for performance reason.

https://answers.sap.com/questions/12984325/how-do-you-check-if-projection-or-aggregation-is-e.html?c...

Thank you.

Loed

[SergioG_TX]

you will need to break down the logic to get the populated values in a branch of your view... then the null values in another branch w a different join... then union the results from both subsets of data.