HANA Joins - Record Counts - Basic Query

[Former Member]

Hi Expert - I am new to HANA and found odd behavior , I need to know the reason for the same. Its a simple inner join between two tables.

ECC Billing Header Table

SELECT count(*)  FROM "ECC"."VBRK" -> 199407

ECC Detail Table

SELECT count(*)  FROM "ECC"."VBRP" -> 286294

When I join both the tables and ran below Query I got the count  - > 1717452

SELECT count(T1.VBELN)

     from "ECC"."VBRK" AS T1

        INNER JOIN

    "ECC"."VBRP" AS T2

        ON T1."VBELN" = T2."VBELN";

I am not able to understand the logic of count returned by the above query.

Can anyone help me in understanding this.

Thanks

R

[Former Member · Accepted solution]

What result would you expect? 286294 rows, so every individual detail and its related header? Then you have to join both tables in the first place on all relevant join fields. You have missed the MANDT join condition. Correct would be:

SELECT count(T1.VBELN)

     from "ECC"."VBRK" AS T1

        INNER JOIN

    "ECC"."VBRP" AS T2

        ON T1."VBELN" = T2."VBELN" AND T1.MANDT = T2.MANDT;

Which result set do you now get?

[Former Member · Accepted solution]

Perfect Martin. Thanks a lot. I was missing MANDT Join.

Its returning correct records now.

Thanks Again.

[patrickbachmann]

It seems as though it's not finding a match for certain document numbers in the detail table.  To find the missing documents you could do something like this;

SELECT T1.VBELN

     from "ECC"."VBRK" T1

        LEFT OUTER JOIN

    "ECC"."VBRP" T2

        ON T1."VBELN" = T2."VBELN" WHERE T2.VBELN IS NULL;

-Patrick