Problem with HANA SQL Join Query

[Former Member]

Hi Experts,

I have a requirement to select all Business Partner from table BUT100 with Roles 'BUP001' and 'ZPREBP' ( join with table BUT100 ), as well I need the job function of the partner in table BUT000 (join with table BUT051 to get the field PAFKT - job function).

When I first join table BUT000 and BUT100 to get BP with roles 'BUP001' and 'ZPREBP', the number of records are 912218:

SELECT A.PARTNER, B.RLTYP

FROM SCHEMA.BUT000 A

INNER JOIN SCHEMA.BUT100 B ON A.PARTNER = B.PARTNER

WHERE B.RLTYP IN( 'BUP001', 'ZPREBP')

After that i join the above SQL with table BUT051 in order to get the field PAFKT - job function, the number of records become 914877

SELECT A.PARTNER, B.RLTYP, C.PAFKT

FROM SCHEMA.BUT000 A

INNER JOIN SCHEMA.BUT100 B ON A.PARTNER = B.PARTNER

LEFT   JOIN SCHEMA.BUT051 C ON A.PARTNER = C.PARTNER2

WHERE B.RLTYP IN( 'BUP001', 'ZPREBP')

The number of record from 1st SQL and 2nd SQL is not match. In the final result i was supposed to get only 912218 of records right?

Is the any mistake i made in the SQL script?

Regards,

Alexender

[patrickbachmann]

It seems to me it's because you are using a left outer join and it's not finding a match in both tables.  A left outer join will return all records of  each table even if a match is not found.

[Former Member]

Hi Patrick,

I am actually using ABAP report to access the HANA database with ABAP Database Connectivity (ADBC).

It seems like having duplicate records in the table. This might be the reason number of return records are different.

I  check the duplicated record in the itab as following:

SORT lt_contact_data BY partner.

DELETE ADJACENT DUPLICATES FROM lt_contact_data COMPARING partner.

Is the any ways to check duplicate records with HANA SQL Script?

Regards,

Alexender

[henrique_pinto]

Actually, a left outer should return all entries in the left (prior) table, having or not a match.

What you've described is a full outer join.

[patrickbachmann]

Ahh, I'm sure there is a way however I have not done this yet. 

[patrickbachmann]

My mistake you are exactly correct Henrique. I was thinking of full outer.

[Former Member]

Hi,

So i should not used LEFT JOIN?

[Former Member]

HI,

If you wanted to check for duplicate records you could run the following SQL;

Where the PK1.. relates to the fields that would make up the Primary Key on the table

select count(*),

PK1,PK2,PK3  from SourceTable

group by PK1,PK2,PK3

having count(*)>1

[patrickbachmann]

No the LEFT outer join should be fine.

[former_member182046]

Please note that you do not specify a unique key for table BUT051. There can be several records for PARTNER2 = your partner number, which explains the larger number of records.

Best,

Thorsten

[Former Member]

Hi Thorsten,

This is why there are duplicate records occurs more than one times right?

Okay, i got it then.

Thanks.