on 12-03-2012 1:50 PM
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
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
User | Count |
---|---|
90 | |
10 | |
10 | |
10 | |
7 | |
7 | |
6 | |
5 | |
4 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.